MySQL AUTO_INCREMENT พร้อมตัวอย่าง

การเพิ่มอัตโนมัติคืออะไร?

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

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

เมื่อใช้การเพิ่มขึ้นอัตโนมัติ?

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

คีย์หลักต้องมีค่าที่ไม่ซ้ำกัน เนื่องจากใช้ระบุแต่ละแถวในฐานข้อมูลได้อย่างเฉพาะเจาะจง แต่เราจะมั่นใจได้อย่างไรว่าคีย์หลักนั้นมีค่าไม่ซ้ำกันเสมอ?

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

เพื่อหลีกเลี่ยงความซับซ้อนดังกล่าว และเพื่อให้แน่ใจว่าคีย์หลักนั้นไม่ซ้ำกันเสมอ เราสามารถใช้ MySQL คุณสมบัติการเพิ่มค่าอัตโนมัติ (Auto Increment) ใช้สำหรับสร้างคีย์หลัก การเพิ่มค่าอัตโนมัติใช้กับชนิดข้อมูล INT ชนิดข้อมูล INT รองรับทั้งค่าที่มีเครื่องหมายและไม่มีเครื่องหมาย ชนิดข้อมูลที่ไม่มีเครื่องหมายสามารถเก็บได้เฉพาะตัวเลขบวกเท่านั้น ตามหลักปฏิบัติที่ดีที่สุด แนะนำให้กำหนดข้อจำกัดที่ไม่มีเครื่องหมาย (unsigned constraint) บนคีย์หลักที่ใช้การเพิ่มค่าอัตโนมัติ

ไวยากรณ์การเพิ่มขึ้นอัตโนมัติ

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

CREATE TABLE `categories` (
  `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_name` varchar(150) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
);

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

หมายเหตุ คีย์เวิร์ด UNSIGNED จะเพิ่มช่วงบวกของคอลัมน์เป็นสองเท่า และความกว้างในการแสดงผลที่เคยเขียนเป็น int(11) นั้นเลิกใช้แล้ว MySQL ตั้งแต่ 8.0.17 เป็นต้นไป แบบธรรมดา int เป็นรูปแบบปัจจุบัน

โดยค่าเริ่มต้น ค่าเริ่มต้นของ AUTO_INCREMENT คือ 1 และจะเพิ่มขึ้นทีละ 1 สำหรับแต่ละเรคอร์ดใหม่

เรามาตรวจสอบเนื้อหาปัจจุบันของตารางหมวดหมู่กันเถอะ

SELECT * FROM `categories`;

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

category_id
category_name
remarks




1
Comedy
Movies with humour


2
Romantic
Love stories


3
Epic
Story acient movies


4
Horror
NULL


5
Science Fiction
NULL


6
Thriller
NULL


7
Action
NULL


8
Romantic Comedy
NULL

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

INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');

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

category_id
category_name
remarks




1
Comedy
Movies with humour


2
Romantic
Love stories


3
Epic
Story acient movies


4
Horror
NULL


5
Science Fiction
NULL


6
Thriller
NULL


7
Action
NULL


8
Romantic Comedy
NULL


9
Cartoons
NULL

โปรดทราบว่าเราไม่ได้ระบุรหัสหมวดหมู่ MySQL ระบบสร้างขึ้นโดยอัตโนมัติ เนื่องจากรหัสหมวดหมู่ถูกกำหนดให้เพิ่มขึ้นอัตโนมัติ

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

SELECT LAST_INSERT_ID();

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

เคล็ดลับ: ฟังก์ชัน LAST_INSERT_ID() นั้นจำกัดขอบเขตการใช้งานเฉพาะการเชื่อมต่อของคุณเท่านั้น ดังนั้นค่าที่สร้างขึ้นจากการแทรกข้อมูลของผู้ใช้รายอื่นจึงไม่สามารถส่งกลับมายังคุณโดยไม่ได้ตั้งใจได้

วิธีตั้งค่าหรือรีเซ็ตค่าเริ่มต้น AUTO_INCREMENT

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

1. กำหนดค่า ณ เวลาสร้าง เพิ่มข้อความ AUTO_INCREMENT ต่อท้ายคำสั่ง CREATE TABLE แถวแรกที่แทรกเข้าไปจะได้รับหมายเลขนั้นแทนที่จะเป็นเลข 1
2. เปลี่ยนแปลงค่าในตารางที่มีอยู่แล้ว ใช้ แก้ไขตาราง โดยมีข้อความเดียวกัน MySQL ระบบจะยอมรับหมายเลขใหม่ก็ต่อเมื่อหมายเลขนั้นสูงกว่าหมายเลขระบุตัวตนที่ใหญ่ที่สุดที่จัดเก็บไว้ในปัจจุบันเท่านั้น
3. รีเซ็ตตารางที่คุณได้ล้างข้อมูลไปแล้ว คำสั่ง TRUNCATE TABLE จะลบทุกแถวและคืนค่าตัวนับเป็น 1 ในการดำเนินการเพียงครั้งเดียว ซึ่งคำสั่ง DELETE เพียงอย่างเดียวไม่สามารถทำได้
4. ยืนยันการเปลี่ยนแปลง แทรกแถวและอ่านค่าตัวระบุกลับมาด้วย LAST_INSERT_ID() ก่อนที่จะใช้ลำดับใหม่

-- Start a brand-new table at 1000
CREATE TABLE `invoices` (
  `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `amount` decimal(10,2),
  PRIMARY KEY (`invoice_id`)
) AUTO_INCREMENT = 1000;

-- Move the counter on an existing table
ALTER TABLE `categories` AUTO_INCREMENT = 100;

-- Empty the table and reset the counter to 1
TRUNCATE TABLE `categories`;

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

ทำไมจึงมีช่องว่างปรากฏในลำดับ AUTO_INCREMENT?

ไม่ช้าก็เร็ว ตารางจะแสดงตัวเลขระบุเช่น 1, 2, 5, 6 ไม่มีอะไรผิดปกติ ตัวนับถูกออกแบบมาเพื่อรับประกันความไม่ซ้ำกัน ไม่ใช่เพื่อรับประกันลำดับตัวเลขที่ไม่ขาดตอน และจะไม่แสดงค่าเดียวกันซ้ำสองครั้ง

ช่องว่างเกิดขึ้นจากสาเหตุดังต่อไปนี้

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

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

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