MySQL ฟังก์ชัน: สตริง, ตัวเลข, กำหนดโดยผู้ใช้, จัดเก็บ

สิ่งที่เป็น MySQL ฟังก์ชัน?

MySQL สามารถทำได้มากกว่าการจัดเก็บและดึงข้อมูล. นอกจากนี้ยังสามารถ ดำเนินการจัดการข้อมูล ก่อนที่จะดึงข้อมูลหรือบันทึกข้อมูลนั้น นั่นคือจุดเริ่มต้น MySQL ฟังก์ชันเข้ามามีบทบาท ฟังก์ชันเป็นเพียงส่วนของโค้ดที่ทำการประมวลผลบางอย่างแล้วส่งคืนผลลัพธ์ ฟังก์ชันบางฟังก์ชันรับพารามิเตอร์ ในขณะที่ฟังก์ชันบางฟังก์ชันไม่รับพารามิเตอร์ใดๆ

ลองมาดูตัวอย่างสั้นๆ กัน โดยค่าเริ่มต้นแล้ว MySQL บันทึกข้อมูลวันที่ในรูปแบบ “YYYY-MM-DD” สมมติว่าเราได้สร้างแอปพลิเคชันขึ้นมา และผู้ใช้ต้องการให้แสดงวันที่ในรูปแบบ “DD-MM-YYYY” เราสามารถใช้... MySQL มีฟังก์ชันในตัวชื่อ DATE_FORMAT ที่ช่วยให้ทำเช่นนี้ได้ DATE_FORMAT เป็นหนึ่งในฟังก์ชันที่ใช้บ่อยที่สุดใน MySQLและเราจะมาพิจารณารายละเอียดเพิ่มเติมในบทเรียนนี้กัน

ไม่ว่าจะเป็นประเภทใดก็ตาม ฟังก์ชัน ส่งคืนค่าเดียวเสมอ, อาจรับพารามิเตอร์ได้ตั้งแต่ศูนย์ตัวขึ้นไป ภายในวงเล็บ และ สามารถใช้ได้ทุกที่ที่อนุญาตให้ใช้การแสดงออก — ในรายการ SELECT, เงื่อนไข WHERE หรือเงื่อนไข ORDER BY

ทำไมต้องใช้ MySQL ฟังก์ชัน?

เมื่อเรารู้แล้วว่าฟังก์ชันคืออะไร คำถามต่อไปคือ ทำไมเราจึงควรบันทึกงานนี้ลงในฐานข้อมูล

ดังที่แผนภาพด้านบนแสดง ฟังก์ชันจะรับค่าอินพุต ประมวลผลตรรกะภายในระบบฐานข้อมูล และส่งผลลัพธ์เดียวกลับไปยังทุกแอปพลิเคชันที่ร้องขอ

โปรแกรมเมอร์อาจคิดว่า “จะเสียเวลาทำไปทำไม” MySQL ฟังก์ชัน? ผลลัพธ์เดียวกันนี้สามารถทำได้ด้วยการเขียนสคริปต์หรือภาษาโปรแกรม” จริงอยู่ที่เราสามารถทำเช่นนั้นได้โดยการเขียนขั้นตอนในโปรแกรมแอปพลิเคชัน

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

สิ่งนี้จะกลายเป็นปัญหาเมื่อแอปพลิเคชันต้องรวมเข้ากับระบบอื่น เมื่อเราใช้ MySQL ฟังก์ชันต่างๆ เช่น DATE_FORMAT นั้นถูกฝังอยู่ในฐานข้อมูล และแอปพลิเคชันใดๆ ที่ต้องการข้อมูลก็จะได้รับข้อมูลในรูปแบบที่ต้องการ ช่วยลดการทำงานซ้ำซ้อนในส่วนของตรรกะทางธุรกิจและลดความไม่สอดคล้องกันของข้อมูล.

อีกเหตุผลหนึ่งที่ต้องพิจารณา MySQL หน้าที่ของมันคือการช่วยลดปริมาณการรับส่งข้อมูลเครือข่ายในแอปพลิเคชันไคลเอ็นต์/เซิร์ฟเวอร์เลเยอร์ธุรกิจเพียงแค่เรียกใช้ฟังก์ชันที่จัดเก็บไว้ โดยไม่ต้องดึงข้อมูลดิบผ่านเครือข่ายมาประมวลผล โดยเฉลี่ยแล้ว การใช้ฟังก์ชันสามารถช่วยปรับปรุงประสิทธิภาพโดยรวมของระบบได้อย่างมาก

ประเภทของ MySQL ฟังก์ชั่น

เมื่อเราได้ข้อสรุปเกี่ยวกับ “อะไร” และ “ทำไม” แล้ว เราก็สามารถมาดูฟังก์ชันทั้งสามประเภทได้แล้ว MySQL มีให้เลือกใช้ทั้งฟังก์ชันในตัว ฟังก์ชันที่บันทึกไว้ และฟังก์ชันที่ผู้ใช้กำหนดเอง

ฟังก์ชั่น Built-in

MySQL มาพร้อมกับฟังก์ชันในตัวจำนวนหนึ่ง ซึ่งเป็นฟังก์ชันที่ได้ถูกนำไปใช้งานแล้วในโปรแกรม MySQL เซิร์ฟเวอร์ช่วยให้เราสามารถดำเนินการจัดการข้อมูลได้หลายประเภท และแบ่งออกเป็นกลุ่มที่ใช้กันทั่วไปดังต่อไปนี้

• ฟังก์ชันสตริง – ดำเนินการกับชนิดข้อมูลแบบสตริง
• ฟังก์ชันตัวเลข – ดำเนินการกับชนิดข้อมูลตัวเลข
• ฟังก์ชั่นวันที่ – ดำเนินการกับชนิดข้อมูลวันที่
• ฟังก์ชันรวม – ดำเนินการกับประเภทข้อมูลทั้งหมดข้างต้น และสรุปชุดผลลัพธ์
• ฟังก์ชั่นอื่น ๆ - MySQL นอกจากนี้ยังรองรับฟังก์ชันในตัวประเภทอื่นๆ อีกด้วย แต่ในบทเรียนนี้เราจะจำกัดเฉพาะกลุ่มที่กล่าวถึงข้างต้นเท่านั้น

ต่อไปนี้เราจะมาดูรายละเอียดของแต่ละกลุ่มที่กล่าวถึงข้างต้นกัน เราจะอธิบายฟังก์ชันที่ใช้บ่อยที่สุดโดยใช้ฐานข้อมูลตัวอย่าง “Myflixdb” ของเรา

ฟังก์ชันสตริง

ฟังก์ชันสตริงทำงานกับค่าข้อความ ในตารางภาพยนตร์ของเรา ชื่อเรื่องถูกจัดเก็บโดยใช้ตัวอักษรพิมพ์เล็กและพิมพ์ใหญ่ผสมกัน สมมติว่าเราต้องการคำสั่งค้นหาที่ส่งคืนชื่อเรื่องในรูปแบบตัวพิมพ์ใหญ่ ฟังก์ชัน “UCASE” รับสตริงเป็นพารามิเตอร์และแปลงตัวอักษรทุกตัวเป็นตัวพิมพ์ใหญ่ ดังที่สคริปต์ด้านล่างแสดงให้เห็น

SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_title` FROM `movies`;

ที่นี่

• UCASE(`title`) เป็นฟังก์ชันในตัวที่รับชื่อเรื่องเป็นพารามิเตอร์และส่งคืนค่าเป็นตัวพิมพ์ใหญ่
• AS `upper_case_title` กำหนดชื่อเล่นให้กับคอลัมน์ที่คำนวณได้ เพื่อให้ชุดผลลัพธ์มีส่วนหัวที่อ่านง่าย แทนที่จะเป็นนิพจน์ดิบๆ

ดำเนินการสคริปต์ข้างต้นใน MySQL การทดสอบด้วย Workbench กับ Myflixdb ให้ผลลัพธ์ดังที่แสดงด้านล่าง

นอกจาก UCASE แล้ว ยังมีอีกสององค์กรที่ควรค่าแก่การจดจำ: แอลเคส แปลงสตริงให้เป็นตัวพิมพ์เล็ก และ คอนแคต รวมสตริงสองสตริงขึ้นไปเข้าเป็นสตริงเดียว สำหรับรายการทั้งหมด โปรดดูที่... MySQL การอ้างอิงฟังก์ชันสตริง.

ฟังก์ชันตัวเลข

ดังที่กล่าวไว้ก่อนหน้านี้ ฟังก์ชันเชิงตัวเลขจะทำงานกับชนิดข้อมูลตัวเลข นอกจากนี้เรายังสามารถทำการคำนวณทางคณิตศาสตร์กับข้อมูลตัวเลขโดยตรงในคำสั่ง SQL ของเราได้อีกด้วย

ตัวดำเนินการเลขคณิต

MySQL รองรับตัวดำเนินการทางคณิตศาสตร์ต่อไปนี้ ซึ่งสามารถใช้ในการคำนวณในคำสั่ง SQL ได้

ตัวอย่างของการใช้งานตัวดำเนินการแต่ละตัวมีดังต่อไปนี้

การหารจำนวนเต็ม (DIV) — คำสั่ง DIV จะตัดส่วนที่เป็นเศษส่วนทิ้งและแสดงเฉพาะจำนวนเต็มเท่านั้น

SELECT 23 DIV 6;

การรันสคริปต์ข้างต้นจะให้ผลลัพธ์ดังนี้ 3.

ตัวดำเนินการหาร (/) — ต่างจากคำสั่ง DIV ตรงที่ตัวดำเนินการหารจะคงค่าทศนิยมของผลลัพธ์ไว้

SELECT 23 / 6;

การรันสคริปต์ข้างต้นจะให้ผลลัพธ์ดังนี้ 3.8333.

ตtracตัวดำเนินการ (-)

SELECT 23 - 6;

การรันสคริปต์ข้างต้นจะให้ผลลัพธ์ดังนี้ 17.

ตัวดำเนินการบวก (+)

SELECT 23 + 6;

การรันสคริปต์ข้างต้นจะให้ผลลัพธ์ดังนี้ 29.

ตัวดำเนินการคูณ (*)

SELECT 23 * 6 AS `multiplication_result`;

ผลลัพธ์:

ตัวดำเนินการโมดูลัส (% หรือ MOD)

ตัวดำเนินการโมดูลัสหาร N ด้วย M แล้วให้ผลลัพธ์เป็นเศษเหลือ ลองดูตัวอย่างการใช้ตัวดำเนินการโมดูลัส โดยใช้ค่าเดียวกันกับตัวอย่างก่อนหน้านี้

SELECT 23 % 6;
-- OR, equivalently:
SELECT 23 MOD 6;

การเรียกใช้สคริปต์ใดสคริปต์หนึ่งจะให้ผลลัพธ์ดังนี้ 5.

ตอนนี้เรามาดูฟังก์ชันตัวเลขทั่วไปบางส่วนกัน MySQL.

FLOOR – ฟังก์ชันนี้จะลบทศนิยมออกจากตัวเลขและปัดเศษลงให้เป็นจำนวนเต็มที่ใกล้ที่สุด สคริปต์ที่แสดงด้านล่างนี้แสดงวิธีการใช้งาน

SELECT FLOOR(23 / 6) AS `floor_result`;

ผลลัพธ์:

รอบที่ – ฟังก์ชันนี้จะปัดเศษตัวเลขให้เป็นจำนวนเต็มที่ใกล้ที่สุด เนื่องจาก 23 / 6 มีค่าเท่ากับ 3.8333 ดังนั้นฟังก์ชัน ROUND จะคืนค่า 4 ในขณะที่ฟังก์ชัน FLOOR จะคืนค่า 3 — สองฟังก์ชันนี้ไม่สามารถใช้แทนกันได้

SELECT ROUND(23 / 6) AS `round_result`;

ผลลัพธ์:

RAND – ฟังก์ชันนี้สร้างตัวเลขสุ่ม ค่าของตัวเลขสุ่มจะเปลี่ยนไปทุกครั้งที่เรียกใช้ฟังก์ชัน ตัวอย่างสคริปต์ด้านล่างแสดงวิธีการใช้งาน

SELECT RAND() AS `random_result`;

ฟังก์ชั่นวันที่

ฟังก์ชันเกี่ยวกับวันที่ทำงานกับข้อมูลประเภทวันที่และเวลา ฟังก์ชัน DATE_FORMAT เป็นฟังก์ชันที่ช่วยแก้ปัญหา "YYYY-MM-DD เทียบกับ DD-MM-YYYY" ที่กล่าวถึงในบทนำ

รูปแบบวันที่ ฟังก์ชันนี้รับพารามิเตอร์สองตัว ได้แก่ ค่าวันที่ที่จะจัดรูปแบบ และสตริงรูปแบบที่สร้างจากตัวยึดตำแหน่ง สคริปต์ด้านล่างจะส่งคืนวันที่วางจำหน่ายแต่ละรายการในรูปแบบ วัน-เดือน-ปี ตามที่ผู้ใช้ของเราต้องการ

SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date`
FROM `movies`;

ตัวอย่างรูปแบบที่ใช้บ่อยที่สุดมีดังต่อไปนี้

ฟังก์ชันเกี่ยวกับวันที่อีกสามฟังก์ชันนั้นปรากฏให้เห็นอยู่บ่อยครั้งในงานประจำวัน:

• CURDATE() ส่งคืนวันที่ปัจจุบันในรูปแบบ YYYY-MM-DD
• NOW () ส่งคืนวันที่ปัจจุบัน และ เวลา
• DATEDIFF(d1, d2) ฟังก์ชันนี้จะส่งคืนจำนวนวันระหว่างสองวันที่กำหนด ซึ่งเป็นพื้นฐานของรายงานค่าเช่าค้างชำระใดๆ

ดูรายชื่อทั้งหมดได้ที่ MySQL ข้อมูลอ้างอิงฟังก์ชันวันที่และเวลา.

ฟังก์ชั่นที่เก็บไว้

ฟังก์ชันสำเร็จรูปครอบคลุมกรณีทั่วไปอยู่แล้ว แต่เมื่อกฎทางธุรกิจมีความเฉพาะเจาะจงมากขึ้น เราจะต้องเขียนฟังก์ชันของเราเอง ซึ่งนั่นคือหน้าที่ของฟังก์ชันที่จัดเก็บไว้

ฟังก์ชันที่จัดเก็บไว้ทำงานเหมือนกับฟังก์ชันพื้นฐานทั่วไป เพียงแต่คุณเป็นผู้กำหนดเอง เมื่อสร้างแล้ว ฟังก์ชันที่จัดเก็บไว้สามารถนำไปใช้ในคำสั่ง SQL ได้เหมือนกับฟังก์ชันอื่นๆ ทั่วไป ไวยากรณ์พื้นฐานแสดงไว้ด้านล่าง

CREATE FUNCTION sf_name ([parameter(s)])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- procedural statements
END

ที่นี่

• “สร้างฟังก์ชัน sf_name ([พารามิเตอร์])” เป็นสิ่งที่บังคับและบอกให้ทราบ MySQL เซิร์ฟเวอร์จะสร้างฟังก์ชันชื่อ `sf_name` โดยมีพารามิเตอร์เสริมที่กำหนดไว้ภายในวงเล็บ
• “ส่งคืนประเภทข้อมูล” เป็นสิ่งที่จำเป็นและระบุประเภทข้อมูลที่ฟังก์ชันส่งคืน
• “กำหนด” ระบุว่าฟังก์ชันจะส่งคืนค่าเดียวกันเสมอเมื่อมีการป้อนอาร์กิวเมนต์เดียวกัน “ไม่ใช่สิ่งที่แน่นอนตายตัว” ประกาศสิ่งที่ตรงกันข้าม
• “เริ่มต้น … สิ้นสุด” ห่อหุ้มโค้ดขั้นตอนการทำงานที่ฟังก์ชันนั้นดำเนินการ

สมมติว่าเราต้องการทราบว่าภาพยนตร์ที่เช่าเรื่องใดบ้างที่เลยกำหนดส่งคืนแล้ว เราสามารถสร้างฟังก์ชันที่จัดเก็บไว้ซึ่งรับวันที่ส่งคืนเป็นพารามิเตอร์และเปรียบเทียบกับวันที่ปัจจุบันบนเซิร์ฟเวอร์ หากวันที่ปัจจุบันมากกว่าวันที่ส่งคืน แสดงว่าภาพยนตร์เรื่องนั้นเลยกำหนดส่งคืนแล้ว และเราจะส่งคืนค่า "ใช่" มิเช่นนั้นเราจะส่งคืนค่า "ไม่ใช่"

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
NOT DETERMINISTIC
BEGIN
    DECLARE sf_value VARCHAR(3);
    IF CURDATE() > return_date THEN
        SET sf_value = 'Yes';
    ELSEIF CURDATE() <= return_date THEN
        SET sf_value = 'No';
    END IF;
    RETURN sf_value;
END|
DELIMITER ;

⚠️ คำเตือน — ห้ามติดป้ายกำกับฟังก์ชันนี้ว่า DETERMINISTIC เนื้อหาเรียกใช้ CURDATE() ดังนั้นอาร์กิวเมนต์เดียวกันจึงสามารถคืนค่า “ไม่” ในวันนี้และ “ใช่” ในวันพรุ่งนี้ได้ การประกาศฟังก์ชันที่ขึ้นอยู่กับเวลาเป็น DETERMINISTIC จะทำให้ตัวปรับแต่งประสิทธิภาพเข้าใจผิดและไม่ปลอดภัยสำหรับการจำลองแบบตามคำสั่ง ใช้ ไม่ใช่สิ่งที่กำหนดไว้ เมื่อใดก็ตามที่ฟังก์ชัน body เรียกใช้ CURDATE(), NOW() หรือ RAND()

การเรียกใช้สคริปต์ข้างต้นจะสร้างฟังก์ชันที่จัดเก็บไว้ชื่อ `sf_past_movie_return_date` ตอนนี้เรามาทดสอบกันดู

SELECT `movie_id`, `membership_number`, `return_date`, CURDATE(),
       sf_past_movie_return_date(`return_date`) AS `is_overdue`
FROM `movierentals`;

ดำเนินการสคริปต์ข้างต้นใน MySQL การทดสอบด้วย Workbench กับ myflixdb ให้ผลลัพธ์ดังต่อไปนี้

สังเกตว่ามีสองแถวที่เป็นค่า NULL เมื่อ `return_date` เป็น NULL การเปรียบเทียบทั้งสองจะให้ผลลัพธ์เป็น NULL แทนที่จะเป็น TRUE หรือ FALSE ดังนั้นเงื่อนไข IF ทั้งสองจึงทำงาน และฟังก์ชันจะส่งคืนค่า NULL ซึ่งเป็นผลลัพธ์ที่คาดหวังไว้ เนื่องจากภาพยนตร์ที่ไม่ได้ส่งคืนจะไม่มีวันที่ส่งคืนให้เปรียบเทียบ

ฟังก์ชันที่ผู้ใช้กำหนด

เมื่อ SQL เพียงอย่างเดียวไม่เร็วพอ MySQL ซึ่งเป็นทางเลือกที่สาม ฟังก์ชันที่ผู้ใช้กำหนดเอง (UDF) เขียนด้วยภาษาคอมไพล์ เช่น C or C++ฟังก์ชันที่ผู้ใช้กำหนดเอง (UDF) ถูกสร้างขึ้นในไลบรารีที่ใช้ร่วมกัน และลงทะเบียนกับเซิร์ฟเวอร์ เมื่อเพิ่มแล้ว ก็สามารถเรียกใช้งานได้เหมือนฟังก์ชันอื่นๆ เนื่องจาก UDF ทำงานเป็นโค้ดเนทีฟภายในกระบวนการของเซิร์ฟเวอร์ จึงเหมาะสำหรับการคำนวณที่ซับซ้อน แต่หากมีข้อผิดพลาดใน UDF ก็อาจทำให้เซิร์ฟเวอร์ล่มได้ ดังนั้น UDF จึงถูกใช้งานน้อยกว่าฟังก์ชันที่จัดเก็บไว้มาก

ฟังก์ชันในตัว ฟังก์ชันที่จัดเก็บไว้ และฟังก์ชันที่ผู้ใช้กำหนดเอง: คุณควรใช้ฟังก์ชันแบบใด?

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

โดยทั่วไปแล้ว ให้เริ่มต้นด้วยฟังก์ชันสำเร็จรูปก่อน หากไม่มีฟังก์ชันใดเหมาะสม ให้เขียนฟังก์ชันที่จัดเก็บไว้เพื่อให้กฎนั้นอยู่ในที่เดียวกัน ควรใช้ฟังก์ชันที่ผู้ใช้กำหนดเอง (UDF) ก็ต่อเมื่อฟังก์ชันที่จัดเก็บไว้ทำงานช้าเกินไปอย่างเห็นได้ชัดเท่านั้น

คำถามที่พบบ่อย