มายเอสคิวแอล – PlusMagi's Blog By Pitt Phunsanit

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: log query

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

เปิดไฟล์ 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
เพิ่ม[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]
สร้างไฟล์ C:\xampp\logs_mysql\all.txt ขึ้นมา ตัว mysql จะไม่สร้างให้อัตโนมัติถ้าไม่มีไฟล์นี้
restart mysql service ใหม่

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

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

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

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 การทำงาน โครงสร้างเหมือนกัน แต่เขียนไม่เหมือนกัน แทนกันไม่ได้ (:

update ข้อมูลใน MySQL แบบหลายตาราง

กำลังทำ presashop ตัวใหม่อยู่ จำเป็นต้องใส่ต้อง copy feature จากภาษาหนึ่งไปอีกภาษาหนึ่งสำหรับ product 1,600 รายการ ถ้าเลือกใส่ใน backend ปกติถึงทำกัน 3 คนมือก็หงิกอยู่ดี (ยังไม่ต้องคิดว่าถ้าทำพลาดละ) และเพราะ prestashop เก็บข้อมูลโดยแยกแต่ละภาษา ออกไปอีกตารางจึ้งต้องเขียน sub query ตามแบบ
SQL Update column values using subquery
query ของผมเป็น
[code language=”sql”]
UPDATE `feature_value_lang` AS target INNER JOIN (
SELECT `id_feature_value` ,`value`FROM `feature_value_lang`
WHERE `id_lang` = 1
) AS source ON target.`id_feature_value` = source.`id_feature_value`
AND target.`id_lang` = 3
SET target.`value` = source.`value`
[/code]

copy ข้อมูลใน MySQL

บางครั้งต้องการข้อมูลจำนวนมาก เพื่อ test บางอย่าง เช่น ระบบแบ่งหน้า โชคดีที่มายเอสคิวแอลสามารถ copy มาจาก record อื่นๆได้ เช่น
[code language=”sql”]
INSERT INTO tableName (col1, col2, col3, …)
SELECT col1, col2, col3, … FROM tableName
WHERE primarykey = 1
[/code]
สมมุติจะคัดลอกตาราง `cds` ในดาต้าเบส cdcol ที่เป็นตัวอย่างติด xampp มา ถ้าไม่มีสร้างใหม่ได้จาก query
[code language=”sql”]
CREATE TABLE IF NOT EXISTS `cds` (
`titel` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
`interpret` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
`jahr` int(11) DEFAULT NULL,
`id` bigint(20) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

INSERT INTO `cds` (`titel`, `interpret`, `jahr`, `id`) VALUES
(‘Beauty’, ‘Ryuichi Sakamoto’, 1990, 1),
(‘Goodbye Country (Hello Nightclub)’, ‘Groove Armada’, 2001, 4),
(‘Glee’, ‘Bran Van 3000’, 1997, 5);

ALTER TABLE `cds`
ADD PRIMARY KEY (`id`);
[/code]
เราจะ copy เพิ่มโดยคิวรี่
[code language=”sql”]
INSERT INTO `cds`(`titel`, `interpret`, `jahr`)
SELECT `titel`, `interpret`, `jahr`
FROM `cds`
WHERE `id` = 5
[/code]
สังเกตุ จะไม่เลือก ฟิลด์ id เพราะ field มันเป็น primary key หลังจากรันคิวรี่จะเห็นว่าข้อมูลเพิ่มขึ้นมาใหม่เหมือนข้อมูลใน id = 5 ทุกอย่างยกเว้น id

การ import MySQL ขนาดใหญ่

ถ้าใช้ phpmyadmin อาจจะไม่สะดวกนัก จากการที่ต้อง browse ไฟล์ขนาดใหญ่ขึ้นไปถ้าอินเตอร์เน็ตช้า (ทั้งของ server และของเราเอง) อาจจะต้องส่งไฟล์ขึ้นไปหลายๆครั้ง กว่าที่การ upload ไฟล์ขึ้นไปจะสมบูรณ์

แก้โดยเปลี่ยนไปใช้ tool ตัวอื่นช่วย import ไฟล์ sql ขนาดใหญ่เข้า MySQL ไปโหลด BigDump แตกไฟล์ออกมา มีไฟล์เดียวนั่นละ เปิดออกมาแก้ Configuration file ไม่กี่บรรทัด
[sourcecode language=”php”]
// Database configuration
$db_server = ‘localhost’;
$db_name = ‘largeDB’; // ชื่อ db เป้าหมาย
$db_username = ‘root’; // ชื่อ username
$db_password = ”; // รหัสผ่าน
$max_query_lines = 300000; //ใส่ไปเยอะๆแก้ปัญหาเจอ error at this place the current query includes more than 300 dump lines.
[/sourcecode]

วิธีใช้ก็แค่

ใช้ ftp upload file bigdump.php และ ไฟล์ sql ที่ใช้ทั้งหมดขึ้นไปไว้ใน folder เดียวกัน
เปิดหน้าเว็บเรียกURL http://your URL/path to folder/bigdump.php จะเห็นชื่อไฟล์ sql ที่อัพโหลดขึ้นไปกด Start Import เท่านั้นเอง

ดูเพิ่มเติม

ย้ายฐานข้อมูล mysql ขนาดใหญ่

PlusMagi's Blog By Pitt Phunsanit

Tag Archive มายเอสคิวแอล