PlusMagi's Blog By Pitt Phunsanit

plus emotion magic to the knowledge of logic.

Category Archive database

  • Home   /  
  • Archive by category "database"

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

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

  1. เปิดไฟล์ C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe.config
  2. ค้นหาบรรทัด <assemblyBinding
  3. เพิ่มข้อความ
    <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>
  4. ปิดและเปิด 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 ตามปกติเพื่อเลือกข้อมูลที่ต้องการ เช่น[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 ให้ด้วย

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

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

[code language=”php” title=”RabbitMQSendBatch.php”]<?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();[/code]

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

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

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

สร้างส่วน connect กับ RabbitMQ ก่อน[code language=”php” title=”RabbitMQConnection.php”]<?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();
[/code]ค่าคงที่ define(‘AMQP_DEBUG’, true); ใช้เปิด debug mode ของตัว class php-amqplib

การส่งข้อมูล[code language=”php” title=”RabbitMQSend.php”]<?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();
[/code]

จากนั้นรับ message[code language=”php” title=”RabbitMQReceive.php”]<?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();[/code]ข้อมูลจะถูกดำเนินการโดย function callback .ในตัวอย่างจะถูกเขียนไว้ใน RabbitMQReceive.txt และถูกลบออกไปโดย การกำหนดค่าให้ delivery_info

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

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

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

PHP: ติดตั้ง RabbitMQ / AMQP Driver

ที่ทำงานนำ RabbitMQ มาทำระบบ messaging middleware เพื่อที่จะไม่ต้องให้ลูกค้ารอการทำงานเบื้องหลังบางอย่าง โดยเก็บงานที่ไม่เร่งด่วนไว้ทำในภายหลัง

ตัว RabbitMQ เป็นหนึ่งในโปรแกรม Messaging ที่ใช้มาตราฐาน AMQP: Advanced Message Queuing Protocol หรือ ISO/IEC 19464:2014 โปรแกรมตัวอื่นๆ เช่น Apache Qpid และ OpenAMQP

การติดตั้ง

  1. download ตัว driver มาจากเว็บ PECL: amqp โดยเลือกให้ตรงกับ version ของ php
  2. แตกไฟล์ php_amqp.dll ไปไว้ที่ extension_dir เช่น C:\wamp64\bin\php\php5.6.25\ext
  3. แตกไฟล์ rabbitmq.1.dllไปไว้ที่ C:\Windows\System ระวัง! ไม่ใช่ System32 นะครับ
  4. แก้ไฟล์ C:\wamp64\bin\apache\apache2.4.23\bin\php.ini เพิ่ม extension=php_amqp.dll
  5. restart apache ใหม่
  6. เปิด phpinfo ขึ้นมาหา amqp ถ้ามีก็แสดงว่าติดตั้ง extension amqp สำเร็จ

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

Proudly powered by WordPress | Theme: Appointment Blue by Webriti