Tag Archive mysql

Byphunsanit
, , , , ,

MySQL: return json format

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

เริ่มจากการที่เขียน query ตามปกติเพื่อเลือกข้อมูลที่ต้องการ เช่น

SELECT
    *
FROM
    snippets.province
ORDER BY PROVINCE_NAME ASC;

จะเห็นว่ามี column PROVINCE_ID, PROVINCE_CODE, PROVINCE_NAME และ GEO_ID ทีนี้จะแปลง ให้เป็นรูปแบบ json array โดยใช้ JSON_OBJECT วิธีการใช้คือ

SELECT JSON_OBJECT('name1', value1, 'name2', value2, 'name3', value3);

คือ

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;

ค่าค่อยดูเป็น 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;

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

Byphunsanit
, , , , , ,

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

Mysql การดึงข้อมูลจากรูปแบบ json ที่เก็บเป็น array ไว้ จะเขียน query ซับซ้อนกว่าปกตินิดหนึ่งเพราะว่าต้องมีการดึงข้อมูลโดยทราบจำนวนแถว เช่น จากข้อมูล

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]');

c_books มีจำนวนข้อมูลที่เก็บเอาไว้ 18 รายการ

เพื่อความสดวกการเขียน query จะใช้วิธีสร้างตารางเก็บ index ไว้ให้เกาะโดย เราจะสร้างตาราง integers ที่เก็บจำนวนเต็มตั้งแต่ 0 ถึง n ( function JSON_EXTRACT เริ่มจาก index 0 ) เอาไว้ให้ใช้ง่ายๆ เช่น

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')

ส่วนเรื่องจะดึงข้อมูลถึงอันดับที่เท่าไหร่ ถ้าไม่บันทึกไว้ที่คอลัมน์อื่น ก็ทำได้โดยการเขียนเงื่อนไขเช่น ข้อมูลเกี่ยวกับหนังสือทุกเล่ม ต้องมีชื่อเรื่อง (title) ก็จะเขียนประมาณ

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

ตัวอย่าง query ที่เขียนเสร็จแล้ว เช่น

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;

แต่เมื่อใช้ไปนานๆ เจอวิธีที่เขียนได้ง่ายกว่าเดิมคือใช้ JSON_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;

มันง่าย และสั้นกว่าเยอะเลยพิชญ์

Byphunsanit
, , , , , ,

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

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

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

accumulatedScore
คะแนนเก็บ เต็ม 100 คะแนนจะมีน้ำหนักเป็น 50%
midtermScore
คะแนนกลางภาค เต็ม 100 คะแนนจะมีน้ำหนักเป็น 20%
finalScore
คะแนนสอบปลายภาค เต็ม 100 คะแนนจะมีน้ำหนัก 30%
จะเขียนเป็น query ได้เป็น 

((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((finalScore / 100) * 30) AS score

เพื่อที่ทดลอง query ให้ตารางเก็บคะแนนขึ้นมาก่อนเช่น 

-- 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;

จากสูดรที่ดูไม่ซับซ้อนแต่เมื่อเราต้องเขียนคิวรี่ให้ตัดเกรดออกมาพร้อมๆกันมันจะกลายเป็น 

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

จะเกิดอะไรขึ้นถ้าเขียน sql ผิดไปนิดเดียว อาจจะลืมพิมพ์ 

      WHEN
         ((accumulatedScore / 100) * 50) +
((midtermScore / 100) * 20) +
((midtermScore / 100) * 30) <= 70
      THEN
         'C'

เห็นมั๋ย? แค่ใช้คะแนน midtermScore 2 ครั้ง ไปตอนตัดเกรดซักเกรดเอง

ถ้าเปลี่ยนไปใช้ 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

ดูเข้าใจง่ายขึ้นเยอะ โอกาสพลาดก็น้อยลง เขียนผิดก็ตัดเกรดผิดทุกเกรด (เห็นง่ายกว่า) แก้สูตรก็แก้จุดเดียว ชีวิตง่ายขึ้นเยอะ ^_^

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

Byphunsanit
, , , , , ,

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. เพิ่ม 
    # log all query
general_log = on
general_log_file = C:\xampp\logs_mysql\general_log.txt
log_output = file
  3. สร้างไฟล์ C:\xampp\logs_mysql\all.txt ขึ้นมา ตัว mysql จะไม่สร้างให้อัตโนมัติถ้าไม่มีไฟล์นี้
  4. restart mysql service ใหม่

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

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

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

Byphunsanit
, , , , , , , , , , , , , , ,

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

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

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

Byphunsanit
, , , , , , , , , , , , , , , , , ,

MySql ต่างกับ sql server

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

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

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

schema_mysql.php สำหรับ mysql

<?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;
}

และ schema_sqlsrv.php สำหรับ Microsoft sql server

<?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;
}

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

Byphunsanit
, , , , , , , , , , , ,

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

กำลังทำ presashop ตัวใหม่อยู่ จำเป็นต้องใส่ต้อง copy feature จากภาษาหนึ่งไปอีกภาษาหนึ่งสำหรับ product 1,600 รายการ ถ้าเลือกใส่ใน backend ปกติถึงทำกัน 3 คนมือก็หงิกอยู่ดี (ยังไม่ต้องคิดว่าถ้าทำพลาดละ) และเพราะ prestashop เก็บข้อมูลโดยแยกแต่ละภาษา ออกไปอีกตารางจึ้งต้องเขียน sub query ตามแบบ
SQL Update column values using subquery
query ของผมเป็น

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`

Byphunsanit
, , , , , , , , , ,

copy ข้อมูลใน MySQL

บางครั้งต้องการข้อมูลจำนวนมาก เพื่อ test บางอย่าง เช่น ระบบแบ่งหน้า โชคดีที่มายเอสคิวแอลสามารถ copy มาจาก record อื่นๆได้ เช่น

INSERT INTO tableName (col1, col2, col3, ...)
SELECT col1, col2, col3, ... FROM tableName
  WHERE primarykey = 1

สมมุติจะคัดลอกตาราง `cds` ในดาต้าเบส cdcol ที่เป็นตัวอย่างติด xampp มา ถ้าไม่มีสร้างใหม่ได้จาก query

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`);

เราจะ copy เพิ่มโดยคิวรี่

INSERT INTO `cds`(`titel`, `interpret`, `jahr`)
SELECT `titel`, `interpret`, `jahr`
FROM `cds`
WHERE `id` = 5

สังเกตุ จะไม่เลือก ฟิลด์ id เพราะ field มันเป็น primary key หลังจากรันคิวรี่จะเห็นว่าข้อมูลเพิ่มขึ้นมาใหม่เหมือนข้อมูลใน id = 5 ทุกอย่างยกเว้น id

Byphunsanit
, , , , ,

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

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

แก้โดยเปลี่ยนไปใช้ tool ตัวอื่นช่วย import ไฟล์ sql ขนาดใหญ่เข้า MySQL ไปโหลด BigDump แตกไฟล์ออกมา มีไฟล์เดียวนั่นละ เปิดออกมาแก้ Configuration file ไม่กี่บรรทัด

// 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.

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

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

ดูเพิ่มเติม

Byphunsanit
, , , , , ,

Connect to Remote MySQL Server

ถ้าต้องการให้คนอื่นสามารถ ใช้ PHP เข้ามาคิวรี่ข้อมูลในเครื่องเราได้ต้องมีการกำหนดสิทธิกันเล็กน้องครับ หลายวิธีเลย

phpMyAdmin

  1. ไปที่ localhost หรือบางเครื่องจะเป็น 127.0.0.1
  2. users กด Add user
  3. กรอกข้อมูลให้ครบ ตรง Host ใส่ชื่อเครื่อง หรือ ip ของเครื่องที่จะเข้ามาใช้งาน
  4. กลับไปที่ users กด reload the privileges

SQL Query

CREATE USER 'root'@'DEV-SERVER';

GRANT SELECT ,
INSERT ,
UPDATE ,
DELETE ,
CREATE ,
DROP ,
FILE ,
INDEX ,
ALTER ,
CREATE TEMPORARY TABLES ,
CREATE VIEW ,
EVENT,
TRIGGER,
SHOW VIEW ,
CREATE ROUTINE,
ALTER ROUTINE,
EXECUTE ON * . * TO 'root'@'DEV-SERVER' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

Linux Command How to Allow MySQL Client to Connect to Remote MySQL server

Proudly powered by WordPress | Theme: Appointment by Webriti