PlusMagi's Blog By Pitt Phunsanit

plus emotion magic to the knowledge of logic.

Category Archive database

  • Home   /  
  • Archive by category "database"

ติดตั้ง SQL Server Management Studio (smss) แล้วค้าง

ถ้าติดตั้ง SQL Server Management Studio แล้วค้างอยู่ที่ข้อความ “Loading packages. Please wait…” เป็นชั่วโมงไม่ขยับไปไหน แก้ได้โดย

  1. พยาม Cancel ตัวติดตั้งก่อนถ้าทำไม่ได้ให้ end task ไปเลย
  2. ลบไฟล์ทั้งหมดใน temp file
    • โดยใส่ %temp% ใน Windows File Explorer แล้ว enter
    • หรือจะเปิด C:\Users\<username>\AppData\Local\Temp ก็ได้
      จากนั้นลบไฟล์ทั้งหมด ที่สามารถจะลบได้ออกไป
  3. ลบ registry
    • เปิด command prompt โดยสิทธ์ administrator พิมพ์
      reg DELETE “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” /reg:32
      กด enter จะเห็นคำถาม
      Permanently delete the registry key HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio (Yes/No)?
      พิมพ์ yes แล้ว enter
    • หรือเปิด registry editor โดยสิทธ์ administrator แล้วค้นหา “HKLM\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server Management Studio” จากนั้นก็ลบออกไป
  4. restart windows อาจจะเข้าไปลบไฟล์อีกครั้ง
  5. ติดตั้ง smss ตามปกติ

ขอบคุณ How To Fix SSMS Installation Stuck Issue

SQL Server: Kill process ของข้าให้หมด

มีแก้งานที่เขียน query ได้โหดยูเครนมาก (รัสเซียได้พิสูจน์ให้เห็นแล้วว่ากาก) ใช้ cpu สูงมาก ใช้ ram เกือบหมดเม็ค แล้วมีคนใช้งานอยู่พร้อม ๆ กัน T-T เลยหา script มาจัดการดีด connection ที่ใช้อยู่ในเครื่องตัวเองทิ้งยกเว้นที่ใช้อยู่ใน sql server management studio (ssms) และโปรแกรม app ที่กำลังเขียนอยู่

DECLARE @SqlCmd VARCHAR(1000)
  
	,@HostName VARCHAR(100)-- Set the hostname name from which to kill the connections

  
	--SET @HostName = 'Pitt_P'

SET @HostName = HOST_NAME()

SET @SqlCmd = ''  SELECT @Sqlcmd = @SqlCmd + CHAR(13) + CHAR(10) + 'KILL ' + convert(CHAR(10), spid) + ' '
 
FROM master.dbo.sysprocesses
 
WHERE hostname = @HostName
 
	AND DBID <> 0
 
	AND spid <> @@spid PRINT @sqlcmd EXEC(@Sqlcmd)
 
GO

แก้จากต้นแบบ Kill all processes associated with a hostname โดยดึงข้อมูล HOST HOST_NAME แทนที่จะใส่ชื่อเครื่องตัวเองไป

SQL Server: update limit

เทสงานที่มีการบันทึกข้อมูลไว้ ถ้าจะทำอีกครั้งก็ต้อง update กลับไปเป็นเหมือนเดิม แต่เพราะว่าเงื่อนไขมันกว้างมาก ๆ เลยไปอัพเดตทั้ง table เลยมันช้าไป เปลืองทรัพยากร เลยหาวิธีอื่นดู

  • ROWCOUNT อย่างต้องการให้ update แค่ 100 รายการแรก
    • SET ROWCOUNT 100;
      UPDATE table_name
      SET colunm_name = …
      WHERE column_name = …
      SET ROWCOUNT 0;
  • TOP จะง่ายกว่าหน่อย
    • UPDATE TOP(100) table_name
      SET colunm_name = …
      WHERE column_name = …
  • CTE
    • ;WITH CTE AS
      (
      SELECT TOP 100 *
      FROM table_name
      ORDER BY colunm_name
      )
      UPDATE CTE SET colunm_name = …
  • Sub Query
    • UPDATE table_name
      SET column_name =…
      WHERE column_id IN (
      SELECT TOP 100 column_id
      FROM table_name
      WHERE column_name = 0
      )

เลือกเอาแบบที่ชอบได้เลยครับ ตัวที่ผมชอบที่สุดคือ ROWCOUNT เพราะว่าใช้ query จริง ๆ มาวางได้เลย

ติดตั้ง Poor Man’s T-SQL Formatter ใน SSMS รุ่นใหม่ ๆ

Poor Man’s T-SQL Formatter จะติดตั้งได้ใน SQL Server Management Studio ( SSMS ) เวอร์ชั่นเก่า ๆ ได้ แต่ตัวติดตั้งรุ่นใหม่ ๆ เมื่อติดตั้งแล้ว จะไม่สามารถคลิกเมนูของตัว extension ได้ แก้ได้ง่าย ๆ โดย

  1. ปิด SQL Server Management Studio
  2. ถ้ายังไม่ได้ติดตั้ง .Net 2.0 ให้ติดตั้ง .NET Framework 2.0 ก่อนโดยวิธี How-To How to Enable .NET Framework 2.0 and 3.5 in Windows 11
  3. ถ้าต้องการใช้กับ SSMS 19 ต้องติดตั้ง SSMS 18 ในเครื่องก่อน จากนั้นติดตั้ง plugin ตามปกติ แล้ว copy C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Extensions ไปเป็น C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Extensions
  4. เปิดไฟล์ C:\Program Files (x86)\Microsoft SQL Server Management Studio {version}\Common7\IDE\Ssms.exe.config เช่น
    • C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe.config
    • C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\Ssms.exe.config
  5. ค้นหาบรรทัด <assemblyBinding
  6. เพิ่มข้อความใต้<probing privatePat
    <dependentAssembly><assemblyIdentity name="Microsoft.VisualStudio.Shell.12.0" publicKeyToken="b03f5f7f11d50a3a" culture="neutral"/> <bindingRedirect oldVersion="2.0.0.0-15.0.0.0" newVersion="15.0.0.0"/></dependentAssembly>
  7. เปิด SQL Server Management Studio ใหม่

เท่านี้ก็สามารถใช้ Poor Man’s T-SQL Formatter จัดรูปแบบ sql ได้แล้ว

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

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;

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

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

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

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

ไม่นานก็ได้รายชื่อออกมาทั้ง 843 รายการ แต่จำได้ว่ามีห้องทั้งหมด 730 ห้องแล้วที่เกินมาคืออัลลัย ไล่ดูผลที่ออกมาบางห้องมีเจ้าของมากกว่า 1 คน เป็นเรื่องแล้วเพราะถึงมีเจ้าของกี่คนแต่จะใช้สิทธิได้แค่ห้องละ 1 เท่านั้น ดังนั้นสมมุติว่าห้องไหนมีเจ้าของ 2 คน (เจอว่าบางห้องมี 3 เจ้าของ) จะต้องเขียนให้แสดงรายชื่อในแถวเดียวกัน เช่น ‘1xx/xx’, ‘คุณ พิชญ์ พันธุ์สนิท, คุณ xxx พันธุ์สนิท, คุณ xxx พันธุ์สนิท’, ‘69.58’ แทนที่จะอยู่คนและแถว คนและ row

ตอนแรกจะเขียนแบบใช้ sub query แต่รู้สึกว่าซับซ้อนเกินไปหน่อย ดีที่ไปเห็น GROUP_CONCAT() ลองเขียนดูง่ายกว่าใช้ซับคิวรี่เยอะ

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

ข้อมูลออกมาถูกต้องแล้ว แบบเขียนไม่ยากด้วย ^_^

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 ให้ด้วย

PHP: php-amqplib แบบต่อเนื่อง

ในบาง page เราอาจจะต้องการที่จะใช้ queuing หลายตัว แทนที่จะ connect / disconnect หลายๆครั้ง เราสามารถใช้ batch / bulk ในการส่งช้อมูลให้ rabbitmq ในครั้งเดียวได้

<?php

include 'RabbitMQConnection.php';

use PhpAmqpLib\Message\AMQPMessage;

$exchange_name = 'customers';
$queue_name = 'invoices';

/**
 * Declares exchange
 *
 * @param string $exchange_name
 * @param string $type
 * @param bool $passive
 * @param bool $durable
 * @param bool $auto_delete
 * @param bool $internal
 * @param bool $nowait
 * @param array $arguments
 * @param int $ticket
 * @return mixed|null
 */
$channel->exchange_declare($exchange_name, 'fanout', false, true, false);

/**
 * Declares queue, creates if needed
 *
 * @param string $queue
 * @param bool $passive
 * @param bool $durable
 * @param bool $exclusive
 * @param bool $auto_delete
 * @param bool $nowait
 * @param array $arguments
 * @param int $ticket
 * @return mixed|null
 */
 list($queueName, $message_count, $consumer_count) = $channel->queue_declare($queue_name, false, true, false, false);

$properties = [
    'content_type' => 'application/json',
    'delivery_mode' => AMQPMessage::DELIVERY_MODE_PERSISTENT,
];

for ($a = 1; $a <= 1000; $a++) {
    $datas = [
        'id' => str_pad($a, 4, '0', STR_PAD_LEFT),
        'rand' => rand(0, 100),
        'time' => date('Y-m-d H:m:s'),
    ];

	$msg_body = json_encode($datas);

	$msg = new AMQPMessage($msg_body, $properties);

	/**
	 * Publishes a message
	 *
	 * @param AMQPMessage $msg
	 * @param string $exchange
	 * @param string $routing_key
	 * @param bool $mandatory
	 * @param bool $immediate
	 * @param int $ticket
	 */
	$channel->basic_publish($msg, $exchange_name, $queue_name);

    /**
     * Publishes a message
     *
     * @param AMQPMessage $msg
     * @param string $exchange
     * @param string $routing_key
     * @param bool $mandatory
     * @param bool $immediate
     * @param int $ticket
     */
    $channel->basic_publish($msg, $exchange_name, $queue_name);

    echo '<br>' . $msg_body;
}

/**
 * Publish batch
 *
 * @return void
 */
$channel->publish_batch();

$channel->close();
$connection->close();

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

PHP: ใช้ RabbitMQ / AMQP โดย php-amqplib

ถึงจะติดตั้ง extension amqp ไว้แล้วก็จริงแต่เพราะว่าการติดตั้งอะไรเพิ่มให้ระบบเซิร์ฟเวอร์บริษัทจำเป็นต้องมีการขออนุมัติจากคณะกรรมการซะก่อน เพื่อความสดวกจึงใช้ php-amqplib/php-amqplib แทนโดยสามารถติดตั้งได้โดยใช้ composer ได้เลย

สร้างส่วน connect กับ RabbitMQ ก่อน

<?php

/* define('AMQP_DEBUG', true); */

require_once __DIR__ . '/vendor/autoload.php';

use PhpAmqpLib\Connection\AMQPStreamConnection;

$connection = new AMQPStreamConnection('localhost', 5672, 'guest', 'guest');
$channel = $connection->channel();

ค่าคงที่ define(‘AMQP_DEBUG’, true); ใช้เปิด debug mode ของตัว class php-amqplib

การส่งข้อมูล

<?php

include 'RabbitMQConnection.php';

use PhpAmqpLib\Message\AMQPMessage;

$exchange_name = 'customers';
$queue_name = 'invoices';

/**
 * Declares exchange
 *
 * @param string $exchange_name
 * @param string $type
 * @param bool $passive
 * @param bool $durable
 * @param bool $auto_delete
 * @param bool $internal
 * @param bool $nowait
 * @param array $arguments
 * @param int $ticket
 * @return mixed|null
 */
$channel->exchange_declare($exchange_name, 'fanout', false, true, false);

/**
 * Declares queue, creates if needed
 *
 * @param string $queue
 * @param bool $passive
 * @param bool $durable
 * @param bool $exclusive
 * @param bool $auto_delete
 * @param bool $nowait
 * @param array $arguments
 * @param int $ticket
 * @return mixed|null
 */
list($queueName, $message_count, $consumer_count) = $channel->queue_declare($queue_name, false, true, false, false);

$datas = [
    'rand' => rand(0, 100),
    'time' => date('Y-m-d H:m:s'),
];

$msg_body = json_encode($datas);

$properties = [
    'content_type' => 'application/json',
    'delivery_mode' => AMQPMessage::DELIVERY_MODE_PERSISTENT,
];

$msg = new AMQPMessage($msg_body, $properties);

/**
 * Publishes a message
 *
 * @param AMQPMessage $msg
 * @param string $exchange
 * @param string $routing_key
 * @param bool $mandatory
 * @param bool $immediate
 * @param int $ticket
 */
$channel->basic_publish($msg, $exchange_name, $queue_name);

echo '<br>' . $msg_body;

$channel->close();
$connection->close();

จากนั้นรับ message

<?php

include 'RabbitMQConnection.php';

set_time_limit(0);

$exchange_name = 'customers';
$queue_name = 'invoices';

/**
 * Declares exchange
 *
 * @param string $exchange_name
 * @param string $type
 * @param bool $passive
 * @param bool $durable
 * @param bool $auto_delete
 * @param bool $internal
 * @param bool $nowait
 * @param array $arguments
 * @param int $ticket
 * @return mixed|null
 */
$channel->exchange_declare($exchange_name, 'fanout', false, true, false);

/**
 * Declares queue, creates if needed
 *
 * @param string $queue
 * @param bool $passive
 * @param bool $durable
 * @param bool $exclusive
 * @param bool $auto_delete
 * @param bool $nowait
 * @param array $arguments
 * @param int $ticket
 * @return mixed|null
 */
list($queueName, $message_count, $consumer_count) = $channel->queue_declare('', false, false, true, false);
$channel->queue_bind($queue_name, $exchange_name);

$callback = function ($msg) {

    $datas = json_decode($msg->body, true);
    fwrite(fopen('RabbitMQReceive.txt', 'a+'), print_r($datas, true));

    sleep(substr_count($msg->body, '.'));

    /* delete message */
    $msg->delivery_info['channel']->basic_ack($msg->delivery_info['delivery_tag']);
};

/**
 * Starts a queue consumer
 *
 * @param string $queue_name
 * @param string $consumer_tag
 * @param bool $no_local
 * @param bool $no_ack
 * @param bool $exclusive
 * @param bool $nowait
 * @param callback|null $callback
 * @param int|null $ticket
 * @param array $arguments
 * @return mixed|string
 */
$channel->basic_consume($queue_name, '', false, false, false, false, $callback);

while (count($channel->callbacks)) {
    /**
     * Wait for some expected AMQP methods and dispatch to them.
     * Unexpected methods are queued up for later calls to this PHP
     * method.
     *
     * @param array $allowed_methods
     * @param bool $non_blocking
     * @param int $timeout
     * @throws \PhpAmqpLib\Exception\AMQPOutOfBoundsException
     * @throws \PhpAmqpLib\Exception\AMQPRuntimeException
     * @return mixed
     */

    $channel->wait();
}

$channel->close();
$connection->close();

ข้อมูลจะถูกดำเนินการโดย function callback .ในตัวอย่างจะถูกเขียนไว้ใน RabbitMQReceive.txt และถูกลบออกไปโดย การกำหนดค่าให้ delivery_info

หลักการการทำงานคือ

  1. ย้าย process ที่ไม่ต้องทำทันทีไปทำงานทีหลังโดยใช้ การส่ง ตัวแปรที่จำเป็นไปในรูปแบบ message ให้ rabbitmq ทำให้ user ไม่จำเป็นต้องรอและรู้สึกว่าเว็บทำงานให้อย่างรวดเร็ว
  2. message จะถูกเก็บใน queue ที่ชื่อ “invoices”
  3. เมื่อ server ว่าง ก็เรียก message ใน queue ที่เก็บไว้ ดึงข้อมูลออกมา เพื่อนำไปทำงานต่อไป

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

Proudly powered by WordPress | Theme: Appointment Blue by Webriti