PlusMagi's Blog By Pitt Phunsanit

plus emotion magic to the knowledge of logic.

Category Archive MySql

MySQL: return json format

mysql ก็ support json เพราะว่าเดี๋ยวนี้อะไรๆ ก็แลกเปลี่ยนข้อมูลกันโดยใช้ json ถ้าสามารถ query จาก mysql ให้ result ออกมาเป็น json เลย ก็สะดวกที่จะไม่ต้องใช้ java หรือ beanshell ใน joget แปลงผลลัพธ์ให้ยุ่งยาก แค่มี input ซักตัวก็สามารถนำมันไปใช้ได้เลย

เริ่มจากการที่เขียน query ตามปกติเพื่อเลือกข้อมูลที่ต้องการ เช่น[code langage=”sql” title=”select provinces”]SELECT
*
FROM
snippets.province
ORDER BY PROVINCE_NAME ASC;[/code]จะเห็นว่ามี column PROVINCE_ID, PROVINCE_CODE, PROVINCE_NAME และ GEO_ID ทีนี้จะแปลง ให้เป็นรูปแบบ json array โดยใช้ JSON_OBJECT วิธีการใช้คือ[code language=”sql” title=”function JSON_OBJECT”]SELECT JSON_OBJECT(‘name1’, value1, ‘name2’, value2, ‘name3’, value3);[/code]คือ[code language=”sql” title=”function JSON_OBJECT example”]SELECT
JSON_OBJECT(‘PROVINCE_ID’,
PROVINCE_ID,
‘PROVINCE_CODE’,
PROVINCE_CODE,
‘PROVINCE_NAME’,
PROVINCE_NAME,
‘GEO_ID’,
GEO_ID) AS json
FROM
snippets.province
ORDER BY PROVINCE_NAME ASC;[/code]ค่าค่อยดูเป็น json ขึ้นมาหน่อย แต่มันยังอยู่คนละแถว ไม่เป็นไรมีตัวช่วย[code language=”sql” title=”mysql query to json”]SELECT
CONCAT(‘[‘,
GROUP_CONCAT(JSON_OBJECT(‘PROVINCE_ID’,
PROVINCE_ID,
‘PROVINCE_CODE’,
PROVINCE_CODE,
‘PROVINCE_NAME’,
PROVINCE_NAME,
‘GEO_ID’,
GEO_ID)),
‘]’) AS json
FROM
snippets.province
ORDER BY PROVINCE_NAME ASC;[/code]

แค่นี้ก็ได้ result เป็น json สมใจ อ่านตัวอย่างการใช้ได้ที่ javascript: string to JSON.parse()

MySQL: select ข้อมูลจาก json array

Mysql การดึงข้อมูลจากรูปแบบ json ที่เก็บเป็น array ไว้ จะเขียน query ซับซ้อนกว่าปกตินิดหนึ่งเพราะว่าต้องมีการดึงข้อมูลโดยทราบจำนวนแถว เช่น จากข้อมูล[code language=”sql” title=””]CREATE TABLE `app_fd_books` (
`id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`dateCreated` datetime DEFAULT NULL,
`dateModified` datetime DEFAULT NULL,
`c_books` longtext COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `app_fd_books` (`id`,`dateCreated`,`dateModified`,`c_books`) VALUES (‘b04469bd-ac1430f1-12bd2000-a4f7da3a’,’2018-03-03 22:54:56′,’2018-03-03 22:54:56′,'[{\n \"title\": \"สูญสิ้นความเป็นคน\",\n \"author\": \"ดะไซ โอซามุ\",\n \"publisher\": \"JLIT\"\n }, {\n \"title\": \"ถอดรหัสรักออนไลน์\",\n \"author\": \"Aziz Ansari\",\n \"publisher\": \"openworlds\"\n },\n {\n \"title\": \"เพลงรัตติกาลในอินเดีย\",\n \"author\": \"Antonio Tabucchi\",\n \"publisher\": \"อ่านอิตาลี\"\n },\n {\n \"title\": \"อันเดอร์กราวด์\",\n \"author\": \"ผู้เขียน: ฮารูกิ มูราคามิ\",\n \"publisher\": \"กำมะหยี่\"\n },\n {\n \"title\": \"ร้านหนังสือเลขที่ 84 ถนนแชริงครอสส์\",\n \"author\": \"Helene Hanff\",\n \"publisher\": \"Bookmoby Press\"\n },\n {\n \"title\": \"ปรัชญาในภาพยนตร์\",\n \"author\": \"ผู้เขียน: สิตางค์ เจริญวงศ์ และ ฐานชน จันทร์เรือง\",\n \"publisher\": \"ปล่อยสำนักพิมพ์\"\n },\n {\n \"title\": \"ผู้มาเยือนหลังเที่ยงคืน\",\n \"author\": \"แพทริก เนส\",\n \"publisher\": \"เวิร์ดส์วอนเดอร์\"\n },\n {\n \"title\": \"เข้าป่าหาชีวิต\",\n \"author\": \"จอน คราคาวเออร์\",\n \"publisher\": \"มูลนิธิหนังสือเพื่อสังคม\"\n },\n {\n \"title\": \"ไกลกว่ารั้วบ้านของเรา\",\n \"author\": \"โรสนี นูรฟารีดา\",\n \"publisher\": \"ผจญภัย\"\n },\n {\n \"title\": \"สัจนิยมมหัศจรรย์\",\n \"author\": \"ชูศักดิ์ ภัทรกุลวณิชย์\",\n \"publisher\": \"อ่าน\"\n },\n {\n \"title\": \"เข้าป่าหาชีวิต\",\n \"author\": \"จอน คราคาวเออร์\",\n \"publisher\": \"มูลนิธิหนังสือเพื่อสังคม\"\n },\n {\n \"title\": \"ความลับ 5 ข้อที่คุณต้องค้นให้พบก่อนตาย\",\n \"author\": \"จอห์น อิซโซ\",\n \"publisher\": \"โอ้มายก้อด\"\n },\n {\n \"title\": \"ฝนบางหยด กลายเป็นผีเสื้อ\",\n \"author\": \"ลัดดา สงกระสินธิ์\",\n \"publisher\": \"สมมติ\"\n },\n {\n \"title\": \"Open Diary\",\n \"author\": \"วรพจน์ พันธุ์พงศ์\",\n \"publisher\": \"บางลำพู\"\n },\n {\n \"title\": \"A Gothic Soul\",\n \"author\": \"Jiří Karásek ze Lvovic\",\n \"publisher\": \"Twisted Spoon\"\n },\n {\n \"title\": \"ไกลกว่ารั้วบ้านของเรา\",\n \"author\": \"โรสนี นูรฟารีดา\",\n \"publisher\": \"ผจญภัย\"\n },\n {\n \"title\": \"ประวัติศาสตร์นับศูนย์: สู่การสูญพันธุ์ครั้งที่ 6\",\n \"author\": \"เอลิซาเบธ โคลเบิร์ต\",\n \"publisher\": \"openworlds\"\n },\n {\n \"title\": \"FUTURE : ปัญญาอนาคต\",\n \"author\": \"ภิญโญ ไตรสุริยธรรมา\",\n \"publisher\": \"openbooks\"\n }\n]’);[/code]c_books มีจำนวนข้อมูลที่เก็บเอาไว้ 18 รายการ

เพื่อความสดวกการเขียน query จะใช้วิธีสร้างตารางเก็บ index ไว้ให้เกาะโดย เราจะสร้างตาราง integers ที่เก็บจำนวนเต็มตั้งแต่ 0 ถึง n ( function JSON_EXTRACT เริ่มจาก index 0 ) เอาไว้ให้ใช้ง่ายๆ เช่น[code language=”sql” title=”integers”]CREATE TABLE `integers` (
`row_number` int(4) NOT NULL,
PRIMARY KEY (`row_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO integers (row_number) VALUES (‘0’) , (‘1’) , (‘2’) , (‘3’) , (‘4’) , (‘5’) , (‘6’) , (‘7’) , (‘8’) , (‘9′) , (’10’) , (’11’) , (’12’) , (’13’) , (’14’) , (’15’) , (’16’) , (’17’) , (’18’) , (’19’) , (’20’) , (’21’) , (’22’) , (’23’) , (’24’) , (’25’) , (’26’) , (’27’) , (’28’) , (’29’) , (’30’) , (’31’) , (’32’) , (’33’) , (’34’) , (’35’) , (’36’) , (’37’) , (’38’) , (’39’) , (’40’) , (’41’) , (’42’) , (’43’) , (’44’) , (’45’) , (’46’) , (’47’) , (’48’) , (’49’) , (’50’) , (’51’) , (’52’) , (’53’) , (’54’) , (’55’) , (’56’) , (’57’) , (’58’) , (’59’) , (’60’) , (’61’) , (’62’) , (’63’) , (’64’) , (’65’) , (’66’) , (’67’) , (’68’) , (’69’) , (’70’) , (’71’) , (’72’) , (’73’) , (’74’) , (’75’) , (’76’) , (’77’) , (’78’) , (’79’) , (’80’) , (’81’) , (’82’) , (’83’) , (’84’) , (’85’) , (’86’) , (’87’) , (’88’) , (’89’) , (’90’) , (’91’) , (’92’) , (’93’) , (’94’) , (’95’) , (’96’) , (’97’) , (’98’) , (’99’) , (‘100’)[/code]

ส่วนเรื่องจะดึงข้อมูลถึงอันดับที่เท่าไหร่ ถ้าไม่บันทึกไว้ที่คอลัมน์อื่น ก็ทำได้โดยการเขียนเงื่อนไขเช่น ข้อมูลเกี่ยวกับหนังสือทุกเล่ม ต้องมีชื่อเรื่อง (title) ก็จะเขียนประมาณ[code language=”sql” title=”exit condition”]AND (SELECT
TRIM(BOTH ‘"’ FROM JSON_EXTRACT(datas.c_books,
CONCAT(‘$[‘, row_number, ‘].title’)))
FROM
app_fd_books
WHERE
id = ‘b04469bd-ac1430f1-12bd2000-a4f7da3a’) IS NOT NULL[/code]

ตัวอย่าง query ที่เขียนเสร็จแล้ว เช่น[code language=”sql” title=”MySQL data form JSON Array with condition”]SELECT
row_number,
TRIM(BOTH ‘"’ FROM JSON_EXTRACT(datas.c_books,
CONCAT(‘$[‘, row_number, ‘].title’))) AS title,
TRIM(BOTH ‘"’ FROM JSON_EXTRACT(datas.c_books,
CONCAT(‘$[‘, row_number, ‘].author’))) AS author,
TRIM(BOTH ‘"’ FROM JSON_EXTRACT(datas.c_books,
CONCAT(‘$[‘, row_number, ‘].publisher’))) AS publisher
FROM
app_fd_books AS datas,
(SELECT
row_number AS row_number
FROM
integers) AS length
WHERE
datas.id = ‘b04469bd-ac1430f1-12bd2000-a4f7da3a’
AND (SELECT
TRIM(BOTH ‘"’ FROM JSON_EXTRACT(datas.c_books,
CONCAT(‘$[‘, row_number, ‘].title’)))
FROM
app_fd_books
WHERE
id = ‘b04469bd-ac1430f1-12bd2000-a4f7da3a’) IS NOT NULL
ORDER BY row_number;[/code]

แต่เมื่อใช้ไปนานๆ เจอวิธีที่เขียนได้ง่ายกว่าเดิมคือใช้ JSON_LENGTH ก็จะได้รูปแบบ[code language=”sql” title=”MySQL data form JSON Array with length”]SELECT
row_number,
TRIM(BOTH ‘"’ FROM JSON_EXTRACT(datas.c_books,
CONCAT(‘$[‘, row_number, ‘].title’))) AS title,
TRIM(BOTH ‘"’ FROM JSON_EXTRACT(datas.c_books,
CONCAT(‘$[‘, row_number, ‘].author’))) AS author,
TRIM(BOTH ‘"’ FROM JSON_EXTRACT(datas.c_books,
CONCAT(‘$[‘, row_number, ‘].publisher’))) AS publisher
FROM
integers AS i,
app_fd_books AS datas
WHERE
datas.id = ‘b04469bd-ac1430f1-12bd2000-a4f7da3a’
AND i.row_number < JSON_LENGTH(datas.c_books)
ORDER BY row_number;[/code]มันง่าย และสั้นกว่าเยอะเลยพิชญ์

mySql: แสดงข้อมูลหลายแถวไว้ในชุดเดียวคอลัมน์เดียว

ตอนกำลังทำข้อมูลทะเบียนลูกบ้านเพื่อที่จะได้ทำรายชื่อคนที่มีสิทธิที่จะออกเสียงในที่ประชุมนิติของหมู่บ้าน ดูๆ น่าจะไม่ยากแค่เขียน query ง่ายๆ มีเลขที่ห้อง ชื่อเจ้าของ จำนวนพื้นที่ แบบ[code language=”sql” title=”รายชื่อผู้ถือกรรมสิทธิ์รายบุคล”]SELECT
CONCAT(h.house_no, ‘/’, h.house_no_sub) AS houseNo,
CONCAT(p.title, ‘ ‘, p.name, ‘ ‘, p.surname) AS owner,
h.squareMeter
FROM
v1_house AS h
LEFT JOIN
v1_house_owner AS ho ON h.house_id = ho.house_id
LEFT JOIN
v1_peoples AS p ON ho.people_id = p.people_id
ORDER BY h.house_id ASC[/code]ไม่นานก็ได้รายชื่อออกมาทั้ง 843 รายการ แต่จำได้ว่ามีห้องทั้งหมด 730 ห้องแล้วที่เกินมาคืออัลลัย ไล่ดูผลที่ออกมาบางห้องมีเจ้าของมากกว่า 1 คน เป็นเรื่องแล้วเพราะถึงมีเจ้าของกี่คนแต่จะใช้สิทธิได้แค่ห้องละ 1 เท่านั้น ดังนั้นสมมุติว่าห้องไหนมีเจ้าของ 2 คน (เจอว่าบางห้องมี 3 เจ้าของ) จะต้องเขียนให้แสดงรายชื่อในแถวเดียวกัน เช่น ‘1xx/xx’, ‘คุณ พิชญ์ พันธุ์สนิท, คุณ xxx พันธุ์สนิท, คุณ xxx พันธุ์สนิท’, ‘69.58’ แทนที่จะอยู่คนและแถว คนและ row

ตอนแรกจะเขียนแบบใช้ sub query แต่รู้สึกว่าซับซ้อนเกินไปหน่อย ดีที่ไปเห็น GROUP_CONCAT() ลองเขียนดูง่ายกว่าใช้ซับคิวรี่เยอะ[code language=”sql” title=”รายชื่อผู้ถือกรรมสิทธิ์รายห้อง”]SELECT
CONCAT(h.house_no, ‘/’, h.house_no_sub) AS houseNo,
GROUP_CONCAT(CONCAT(p.title, ‘ ‘, p.name, ‘ ‘, p.surname),
‘ ‘) AS owner,
h.squareMeter
FROM
v1_house AS h
LEFT JOIN
v1_house_owner AS ho ON h.house_id = ho.house_id
LEFT JOIN
v1_peoples AS p ON ho.people_id = p.people_id
GROUP BY h.house_id
ORDER BY h.house_id ASC , ho.people_id ASC[/code]ข้อมูลออกมาถูกต้องแล้ว แบบเขียนไม่ยากด้วย ^_^

MySQL: ใช้ตัวแปร variable

การใช้ตัวแปรใน mysql จะช่วยให้เขียน sql query ได้สั้นและเข้าใจได้ง่ายขึ้น อย่างในกรณีที่นำค่าจากการคำนวณมาค่าหนึ่ง แล้วต้องนำไปใช้แสดงผลใน column อื่นๆ อีกครั้ง เช่น การนำผลที่ได้มาแสดงเป็นช่วงแบบตามขั้นบันได แบ่งช่วงวันที่ออกเป็นกลุ่มๆ หรือการตัดเกรด

สมมุติว่า ต้องตัดเกรดให้เด็กตามช่วงคะแนน โดย

accumulatedScore
คะแนนเก็บ เต็ม 100 คะแนนจะมีน้ำหนักเป็น 50%
midtermScore
คะแนนกลางภาค เต็ม 100 คะแนนจะมีน้ำหนักเป็น 20%
finalScore
คะแนนสอบปลายภาค เต็ม 100 คะแนนจะมีน้ำหนัก 30%
จะเขียนเป็น query ได้เป็น [code language=”sql” title=”calculate score query”]((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((finalScore / 100) * 30) AS score[/code]

เพื่อที่ทดลอง query ให้ตารางเก็บคะแนนขึ้นมาก่อนเช่น [code language=”sql” title=”schoolReport table”]– phpMyAdmin SQL Dump
— version 4.7.0
— https://www.phpmyadmin.net/
—
— Host: 127.0.0.1
— Generation Time: Oct 12, 2017 at 07:46 PM
— Server version: 10.1.25-MariaDB
— PHP Version: 7.1.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

—
— Database: `snippets`
—

— ——————————————————–

—
— Table structure for table `schoolReport`
—

CREATE TABLE `schoolreport` (
`studen_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`accumulatedScore` int(11) NOT NULL,
`midtermScore` int(11) NOT NULL,
`finalScore` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

—
— Dumping data for table `schoolReport`
—

INSERT INTO `schoolReport` (`studen_id`, `course_id`, `accumulatedScore`, `midtermScore`, `finalScore`) VALUES
(1, 1, 55, 76, 74),
(1, 2, 74, 74, 74),
(1, 3, 43, 76, 75),
(1, 4, 47, 45, 57),
(1, 5, 71, 45, 72),
(2, 1, 45, 85, 74),
(2, 2, 65, 47, 47),
(2, 3, 56, 85, 20),
(2, 4, 37, 75, 42),
(2, 5, 65, 35, 74);

—
— Indexes for dumped tables
—

—
— Indexes for table `schoolReport`
—
ALTER TABLE `schoolReport`
ADD UNIQUE KEY `studen_id` (`studen_id`,`course_id`);
COMMIT;
[/code]

จากสูดรที่ดูไม่ซับซ้อนแต่เมื่อเราต้องเขียนคิวรี่ให้ตัดเกรดออกมาพร้อมๆกันมันจะกลายเป็น [code language=”sql” title=”query score”]SELECT
studen_id,
course_id,
accumulatedScore,
midtermScore,
finalScore,
((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((finalScore / 100) * 30) AS score,
CASE
WHEN
((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((finalScore / 100) * 30) < 50
THEN
‘F’
WHEN
((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((finalScore / 100) * 30) <= 60
THEN
‘D’
WHEN
((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((finalScore / 100) * 30) <= 70
THEN
‘C’
WHEN
((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((finalScore / 100) * 30) <= 80
THEN
‘B’
else
‘A’
END
AS grade
FROM
schoolReport[/code] จะเกิดอะไรขึ้นถ้าเขียน sql ผิดไปนิดเดียว อาจจะลืมพิมพ์ [code language=”sql” title=”query calculate grade”] WHEN
((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((midtermScore / 100) * 30) <= 70
THEN
‘C'[/code] เห็นมั๋ย? แค่ใช้คะแนน midtermScore 2 ครั้ง ไปตอนตัดเกรดซักเกรดเอง

ถ้าเปลี่ยนไปใช้ query แบบใช้ตัวแปร[code language=”sql” title=”mysql variable query”]SELECT
studen_id,
course_id,
accumulatedScore,
midtermScore,
finalScore,
@score := ((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((finalScore / 100) * 30) AS score,
CASE
WHEN
@score < 50
THEN
‘F’
WHEN
@score <= 60
THEN
‘D’
WHEN
@score <= 70
THEN
‘C’
WHEN
@score <= 80
THEN
‘B’
else
‘A’
END
AS grade
FROM
schoolReport[/code] ดูเข้าใจง่ายขึ้นเยอะ โอกาสพลาดก็น้อยลง เขียนผิดก็ตัดเกรดผิดทุกเกรด (เห็นง่ายกว่า) แก้สูตรก็แก้จุดเดียว ชีวิตง่ายขึ้นเยอะ ^_^

สรุปการสร้างตัวแปร @ชื่อตัวแปร := (เขียน : ติดกับ = เสมอ) สูตร เพื่อความสวยงาม และใช้ง่ายอย่าลืมใส่ AS alias name ให้ด้วย

update table อัตโนมัติ

สามารถให้ mysql หรือ MariaDB update หรือแก้ไขข้อมูลได้โดยอัตโนมัติทุกครั้งที่ insert, update และ delete ได้โดยใช้ trigger ช่วย จะยกตัวอย่างโดยสมมุติตาราง users โดยถ้า มีการเพิ่มข้อมูลมาใหม่ให้สุ่ม salt และ hash รหัสผ่านให้โดยอัตโนมัติ และเพื่อความปลอดภัยให้เปลี่ยน salt และ hash ใหม่ทุกครั้งที่ password เปลี่ยนไป ถ้าไม่เข้าใจว่า salt คืออะไร ขอเชิญอ่านจากเรื่อง login แบบปลอดภัย

สร้างตาราง users ก่อนโดยใช้[code language=”sql” title=”create table users”]CREATE TABLE `users` (
`user_id` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` char(32) NOT NULL,
`salt` char(5) NOT NULL,
`password_hash` char(32) NOT NULL,
`email` varchar(254) NOT NULL,
`date_create` datetime DEFAULT NULL,
`date_update` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users`
ADD PRIMARY KEY (`user_id`);

ALTER TABLE `users`
MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
[/code]

สร้าง trigger โดยใช้โครงสร้าง[code language=”text” title=”mysql trigger syntax”]DELIMITER $$
CREATE TRIGGER `ชื่อ trigger ` AFERT หรือ BEFORE INSERT หรือ UPDATE หรือ DELETE ON `ตาราง ` FOR EACH ROW BEGIN
…
SET NEW.`ชื่อฟิลย์ ` = ค่าใหม่;
…
END
$$
DELIMITER ;
…
[/code]

.OLD
ค่าใน record เดิมก่อน update
.NEW
ค่าใหม่ที่จะเข้ามา update เปลี่ยนค่าเดิม

trigger ของตาราง users ในแบบที่เราต้องการจะเขียนเป็น[code language=”sql” title=”automatic update by trigger”]–
— Triggers `users`
—
DELIMITER $$
CREATE TRIGGER `users_passwordhash_insert` BEFORE INSERT ON `users` FOR EACH ROW BEGIN
SET @string := ‘abcdefghijklmnopqrstuvwxyz0123456789’;
SET @i := 1;
SET @random := ”;

WHILE (@i <= 5) DO
SET @random := CONCAT(@random, SUBSTRING(@string, FLOOR(RAND() * 36 + 1), 1));
SET @i := @i + 1;
END WHILE;

SET NEW.`date_create` = NOW();
SET NEW.`salt` = @random;
SET NEW.`password_hash` = MD5(CONCAT(NEW.`password`, @random));
END
$$
DELIMITER ;
—
DELIMITER $$
CREATE TRIGGER `users_passwordhash_update` BEFORE UPDATE ON `users` FOR EACH ROW BEGIN
SET @string := ‘abcdefghijklmnopqrstuvwxyz0123456789’;
SET @i := 1;
SET @random := ”;

WHILE (@i <= 5) DO
SET @random := CONCAT(@random, SUBSTRING(@string, FLOOR(RAND() * 36 + 1), 1));
SET @i := @i + 1;
END WHILE;

SET NEW.`date_update` = NOW();
SET NEW.`salt` = @random;
SET NEW.`password_hash` = MD5(CONCAT(NEW.`password`, @random));
END
$$
DELIMITER ;[/code]

ทดลองเพิ่ม และเปลี่ยนข้อมูลดูครับตัว salt และ password_hash จะต้องเปลี่ยนทุกครั้ง ในการใช้งานจริง ให้ลบฟิลย์ password ออกและแก้ trigger ใหม่ เพราะไม่ควรเก็บ password เป็นข้อความธรรมดา (plain text)

Mysql: log query

debug ระบบที่ใช้ mysql แต่หาไม่เจอว่าข้อมูลเปลี่ยนที่จุดไหน เลยต้อง log ทุกๆ sql query ที่ส่งไปให้ mysql server

  1. เปิดไฟล์ config ของ mysql ขึ้นมา โดยแต่ละระบบจะไม่เหมือนกัน
    Ubuntu/Debian
    /etc/mysql/my.cnf
    wamp
    c:\wamp\bin\mysql\mysqlx.y.z\my.ini
    Windows
    c:\ProgramData\MySQL\MySQL Server 5.x
    xampp
    c:\xampp\mysql\bin\my.ini
  2. เพิ่ม[code language=”text” title=”my.ini”]
    # log all query
    general_log = on
    general_log_file = C:\xampp\logs_mysql\general_log.txt
    log_output = file[/code]
  3. สร้างไฟล์ C:\xampp\logs_mysql\all.txt ขึ้นมา ตัว mysql จะไม่สร้างให้อัตโนมัติถ้าไม่มีไฟล์นี้
  4. restart mysql service ใหม่

ถ้าเปลี่ยน config ให้ log_output = table log จะถูกบันทึกไว้ในตาราง general_log ใน database mysql แทน

ควรเปิดใช้เมื่อจำเป็นเท่านั้น ไม่ควรเปิดทิ้งไว้เพราะจะทำให้ mysql ทำงานช้าลง

อ่านเพิ่มเติม

โปรแกรมจัดการ mysql ง่ายๆ เร็วๆ

การจัดการ database คู่บุญ PHP อย่าง MySql หรือ mariadb ร้อยทั้งร้อย และทุกโฮสต์เตรียมเอาไว้ให้ใช้คือ phpMyAdmin แต่มันตัวอ้วนใหญ่ขึ้นทุกๆปี ตามลูกเล่นที่มากขึ้น ถ้ารีบเอางานขึ้นหรือเตรียมไว้เป็นช่องทางฉุกเฺฉินขอแนะนำ Adminer เป็นมายเอสคิวแอล management ที่ตัวเล็กกว่าเยอะ ฟรี ที่สำคัญคือมีไฟล์แค่ไฟล์เดียว

การติกตั้งแค่โหลดมากจากเว็บ เปลี่ยนชื่อซะหน่อยเพื่อความปลอดภัย ให้เป็นชื่อแบบที่เรารู้อยู่แค่คนเดียว แล้ว ftp ขึ้นไป เรียกใช้ได้เลย อาจจะไม่ถนัดเท่าตัวเดิมแต่การติดตั้ง script import export data แบ็คอัพแก้ตารางก็ทำได้ดี สะดวกพอใช้

MySql ต่างกับ sql server

อ่านเจอคำถามนี้ใน facebook

อยากทราบว่าถ้าเราเปลี่ยน database จาก mysql เป็น sql server 2008 นอกจากส่วน connect แล้ว โค๊ดเก่าจากmysql จะสามารถใช้ได้เหมือนเดิมไหมครับ (ผมใช้ codeigniter เขียนครับ)

เลยลองยกตัวอย่างดู โดยใช้ PDO แทน function ของแต่ละ database จะได้เห็นได้ชัดเจน

schema_mysql.php สำหรับ mysql[code language=”php” title=”schema_mysql.php”]
<?php
$database = ‘yii2advanced’;

try {
$dbh = new PDO(
‘mysql:host=localhost;dbname=’ . $database,
‘root’,
”
);
} catch (PDOException $e) {
exit(‘Error!: ‘ . $e->getMessage());
}
$dbh->query(‘SET NAMES utf8’);

switch ($_GET[‘op’]) {
case ‘columns’:{
$table = $_GET[‘table’];

$sql = "SELECT `COLUMN_NAME` AS name
,`DATA_TYPE` AS type
,IFNULL(`CHARACTER_MAXIMUM_LENGTH`, 0) AS maxlength
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘" . $table . "’;";
$sth = $dbh->prepare($sql);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
array_push($results, $row);
}
echo json_encode($results, JSON_NUMERIC_CHECK);
}
}break;
case ‘tables’:{
$sql = ‘SHOW TABLES IN ‘ . $database . ‘;’;
$sth = $dbh->prepare($sql);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_COLUMN)) {
array_push($results, $row);
}
}
echo json_encode($results);
}break;
}
[/code] และ schema_sqlsrv.php สำหรับ Microsoft sql server[code language=”php” title=”schema_sqlsrv.php”]
<?php
$database = ‘TPA’;

try {
$dbh = new PDO(
‘sqlsrv:Server=MAGI\SQLEXPRESS;Database=’ . $database
);
} catch (PDOException $e) {
exit(‘Error!: ‘ . $e->getMessage());
}
$dbh->query(‘SET CHARACTER_SET utf8_unicode_ci’);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

switch ($_GET[‘op’]) {
case ‘columns’:{
$table = $_GET[‘table’];

$sql = "SELECT [COLUMN_NAME] AS name
,[DATA_TYPE] AS type
,ISNULL([CHARACTER_MAXIMUM_LENGTH], 0) AS maxlength
FROM TPA.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘" . $table . "’;";
$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
array_push($results, $row);
}
echo json_encode($results, JSON_NUMERIC_CHECK);
}
}break;
case ‘tables’:{
$sql = ‘SELECT [TABLE_NAME] FROM ‘ . $database . ‘.INFORMATION_SCHEMA.Tables;’;
$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_COLUMN)) {
array_push($results, $row);
}
echo json_encode($results);
}
}break;
}
[/code]

ถึงจะพยามออกแบบ extension ตัวนี้ให้ทำงานได้ทุก database แต่แค่การ connection ก็ต่างกันแล้วแต่ละตัวต่างก็มี ตัวเลือกเพิ่มเติมไม่เหมือนกัน อย่าง การเลือกชุดตัวอักษร SET NAMES utf8 และ SET CHARACTER_SET utf8_unicode_ci ใน sql server (ภาษาไทย ภาษาเดียวก็มีให้เลือกกันเหนื่อยแล้วว่าจะใช้ตัวไหน ลองอ่าน LAB TEST : ความแตกต่างของ SQL THAI Collation แต่ละแบบ) จุดนี้พอจะเข้าใจได้เพราะมาจากคนละบริษัทกัน มาจากคนละความคิด

จุดต่อมา mySql ใช้ ` ` แต่ sql server ใช้ [ ] ในการบอกว่านี่คือชื่อเฉพาะนะ วิธีแก้ก็ง่ายๆ อย่าไปใส่มัน ยกเว้นว่าจะไปใช้ชื่อที่ตรงกับศัพท์สงวน (reserved words) เอาไว้ จำเป็นต้องใช้เพื่อให้ตัว database ไม่สับสน

ถ้าต้องต้องเปลี่ยนฐานข้อมูลก็หลีกเลี่ยงคำพวกนี้ไว้ก่อน

คำสั่งเกี่ยวกับโครงสร้างฐานข้อมูล ต่างเจ้าก็ต่างกัน ทั้งชื่อ type และ key บางครั้งชื่อเดียวกันแต่เก็บข้อมูลได้ไม่เท่ากัน การจะดึงข้อมูลโครงสร้างตารางออกมาก็ใช้คำสั่งต่างกัน ในตัวอย่าง switch case ทั้งสองตัวจะเป็นการ query ดูตารางทั้งหมดในฐานข้อมูลและชนิดของฟิลย์ในตารางทั้งหมด ซึ่งไม่ได้ไกล้เคียงกันเลย (นานๆ จะใช้ที ไม่ต้องไปจำมันก็ได้ ยกเว้นคุณจะเขียน curd ใช้เอง)

การใช้ Abstraction Layers แทนที่จะใช้ Vendor Specific Database Extensions หรือที่เรียกกันว่า native driver ที่เป็นของฐานข้อมูลแต่ละตัว เพราะว่าต้องการที่จะใช้คำสั่งเหมือนๆ กัน ไม่ต้องมาคิดว่าฐานข้อมูลแบบนี้ใช้ function ชื่อนี้ เวลาเปลี่ยน ชนิดฐานข้อมูลก็แค่เปลี่ยนตรง connection ก็พอแล้วไม่ต้องเขียนคำสั่งใหม่ แต่จริงๆ แล้วยังไม่มี Abstraction Layers ตัวไหน หรือของ framework ไหน ไม่ว่าจะเป็น codeigniter, yii, laravel แม้แต่ .net หรือ java ที่แปลง sql query แล้วสามารถทำงานได้เหมือนกันกับทุกๆ ตัว แค่ทำได้ 80% ของทั้งหมดในตัวอย่าง pdo ของ sql server ถ้าไม่ระบุ PDO::ATTR_CURSOR เพิ่มเข้าไป มันก็จะไม่รู้เลยว่า query ออกมามีผลลัพธ์ออกมารึเปล่า และถ้าสังเกตุจะมีคำสั่งที่ใช้ตรวจสอบค่า NULL และแทนด้วยค่าอื่นใน MySQL ใช้ IFNULL แต่ SQL SERVER ใช้ ISNULL การทำงาน โครงสร้างเหมือนกัน แต่เขียนไม่เหมือนกัน แทนกันไม่ได้ (:

หา id ที่ว่างอยู่มา reused

site เดิม content ติดอยู่ใน google แล้วบางส่วน แต่จำเป็นที่จะต้องลบออก แต่ลูกค้าอยากจะให้ link เดิมมันยังเข้าได้ ถึงเนื้อหาใหม่มันจะไม่เกี่ยวกับเรื่องเดิมก็ตาม (เอาเป็นว่าให้เข้ามาที่ไซต์ก่อน)

ก่อนอื่นก็หา id ในตารางที่ว่าอยู่ เพราะโดนลบออกไปแล้วก่อน ใน mySQL ก็เขียนประมาณ

[code language=”sql”]
SELECT a.id + 1 AS START, MIN(b.id) -1 AS END
FROM jos_modules AS a,
jos_modules AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING
START < MIN(b.id)
[/code]

ที่เหลือก็เขียน code เอา id ไป reused โดยตอน INSERT / UPDATE ก็ระบุ id เข้าไปด้วย ใน mysql จะไม่ห้ามถ้าเราจะ recycle id ถึงแม้ว่าเราจะใส่ auto increment ไว้ก็ตาม ขอแค่ id ไม่ซ้ำกับที่มีอยู่เดิม

CR : Sequence gaps in MySQL
RT : https://pitt.plusmagi.com/mysql-auto-number

ทำตาราง yii 2 ให้เก็บหลายภาษา

ต่อจาก ทำ yii 2 ให้รับหลายภาษา หลังจากใส่คำแปรให้ text ต่างๆ แล้วเราจะแยกเก็บ database ภาษาต่างๆ ออกจากกัน คนไทยก็อยากจะอ่านภาษาไทย คนจีนก็อ่านเป็นแต่ภาษาตัวเอง

  1. ติดตั้ง OmgDef/yii2-multilingual-behavior โดยแก้ composer.json ใส่[code language=”php”]
    "require": {
    …
    "omgdef/yii2-multilingual-behavior": "~2.0"
    …
    },
    [/code]run composer update
  2. สร้างตาราง โดยแยกตารางหลักที่เก็บ id และฟิลย์ที่ใช้ร่วมกัน อีกตารางเก็บ field ที่แยกตามภาษาต่างๆเช่น content, title[code language=”sql”]
    CREATE TABLE IF NOT EXISTS `post` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `enabled` tinyint(1) NOT NULL DEFAULT ‘1’,
    `log_created` datetime NOT NULL,
    `log_updated` datetime NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    CREATE TABLE IF NOT EXISTS `postLang` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `post_id` int(11) NOT NULL,
    `language` varchar(6) NOT NULL,
    `title` varchar(255) NOT NULL,
    `content` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    KEY `post_id` (`post_id`),
    KEY `language` (`language`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    ALTER TABLE `postLang`
    ADD CONSTRAINT `postlang_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
    [/code]อย่าลืมใส่ id และ language มันจำเป็นต้องใช้ เก็บว่า record นี้เป็นภาษาอะไร

  3. สร้าง model โดยใช้ gii ตามปกติทั้ง 2 ตาราง
  4. เปิด model ของตารางหลัก ตัวอย่างคือ ..\model\Post.php ใส่ code[code language=”php”]
    …
    use omgdef\multilingual\MultilingualBehavior;
    use omgdef\multilingual\MultilingualQuery;
    …
    public static function find()
    {
    $q = new MultilingualQuery(get_called_class());
    $q->multilingual();
    return $q;
    }

    public function behaviors()
    {
    return [
    ‘ml’ => [
    ‘class’ => MultilingualBehavior::className(),
    ‘languages’ => [
    ‘en’,
    ‘jp’,
    ‘th’,
    ],
    ‘defaultLanguage’ => ‘en’,
    ‘langForeignKey’ => ‘post_id’,
    ‘tableName’ => "{{%postlang}}",
    ‘attributes’ => [
    ‘content’,
    ‘title’,
    ]
    ]
    ];
    }
    …
    [/code]

    • languages ภาษาทั้งหมดที่ใช้ได้
    • defaultLanguage ภาษาเริ่มต้น
    • langForeignKey ใส่ฟิลย์ที่ เชื่อมทั้ง 2 ตารางไว้ด้วยกัน (primary key)
    • tableName ชื่อตารางที่เก็บส่วนแปลภาษาไว้
    • attributes ฟิลย์ทั้งหมด ที่ต้องการทำระบบแปลภาษา
  5. เปิด ไฟล์ _form.php ที่เราใช้กับโมเดลนี้ขึ้นมา ใส่[code language=”php”]
    …
    <?= $form->field($model, ‘title_jp’)->textInput([‘maxlength’ => 255]) ?>
    <?= $form->field($model, ‘title_en’)->textInput([‘maxlength’ => 255]) ?>
    <?= $form->field($model, ‘title_th’)->textInput([‘maxlength’ => 255]) ?>
    …
    [/code]

    6. ทดลอง save ดู ตารางใน database จะเก็บข้อมูลไว้

  6. แก้จุดอื่นๆ อย่าง view index.php ตามรูปแบบ ฟิลย์_ตัวย่อภาษา

Proudly powered by WordPress | Theme: Appointment Blue by Webriti