MySQL AUTO_INCREMENT kèm các ví dụ
Marcus Allen
⚡ Tóm tắt thông minh
MySQL Thuộc tính AUTO_INCREMENT tự động tạo số thứ tự cho một cột số mỗi khi chèn một hàng. Thuộc tính này loại bỏ nhu cầu tính toán mã định danh duy nhất bằng tay, do đó trở thành cách chuẩn để điền vào khóa chính.
Tăng tự động là gì?
Tăng tự động là một hàm hoạt động trên các kiểu dữ liệu số. Nó tự động tạo ra các giá trị số tuần tự mỗi khi một bản ghi được chèn vào bảng cho một trường được xác định là tăng tự động.
Thuộc tính này hoạt động trên mọi kiểu dữ liệu số nguyên, từ TINYINT đến BIGINT. Cột cũng phải được lập chỉ mục, điều này diễn ra tự động khi nó được khai báo là khóa chính.
Khi sử dụng tăng tự động?
Trong bài học về chuẩn hóa cơ sở dữ liệuChúng ta đã xem xét cách thức lưu trữ dữ liệu với độ dư thừa tối thiểu bằng cách lưu trữ dữ liệu vào nhiều bảng nhỏ, liên kết với nhau bằng khóa chính và khóa ngoại.
Khóa chính phải là duy nhất, vì nó xác định duy nhất một hàng trong cơ sở dữ liệu. Nhưng làm thế nào chúng ta có thể đảm bảo rằng khóa chính luôn luôn duy nhất?
Một trong những giải pháp khả thi là sử dụng công thức để tạo khóa chính, công thức này sẽ kiểm tra sự tồn tại của khóa trong bảng trước khi thêm dữ liệu. Cách này có thể hiệu quả, nhưng phương pháp này phức tạp và không hoàn toàn đáng tin cậy. Hai phiên chèn dữ liệu cùng một lúc vẫn có thể đọc cùng một giá trị tối đa và dẫn đến xung đột.
Để tránh sự phức tạp như vậy và để đảm bảo rằng khóa chính luôn duy nhất, chúng ta có thể sử dụng MySQL Tính năng tự động tăng được sử dụng để tạo khóa chính. Tính năng tự động tăng được sử dụng với kiểu dữ liệu INT. Kiểu dữ liệu INT hỗ trợ cả giá trị có dấu và không dấu. Kiểu dữ liệu không dấu chỉ có thể chứa số dương. Theo kinh nghiệm tốt nhất, nên định nghĩa ràng buộc không dấu trên khóa chính tự động tăng.
Cú pháp tăng tự động
Sau khi đã làm rõ lý lẽ, hãy xem xét kịch bản được sử dụng để tạo bảng phân loại phim.
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`) );
Hãy chú ý đến thuộc tính “AUTO_INCREMENT” trên trường category_id. Thuộc tính này khiến mã định danh danh mục được tự động tạo mỗi khi một hàng mới được chèn vào bảng. Mã này không được cung cấp khi chèn dữ liệu vào bảng. MySQL tạo ra nó.
Lưu ý: Từ khóa UNSIGNED nhân đôi phạm vi dương của cột và chiều rộng hiển thị từng được viết dưới dạng int(11) đã bị loại bỏ. MySQL Từ phiên bản 8.0.17 trở đi. Plain int Đây là hình thức hiện tại.
Theo mặc định, giá trị ban đầu của AUTO_INCREMENT là 1, và nó sẽ tăng thêm 1 cho mỗi bản ghi mới.
Chúng ta hãy cùng xem xét nội dung hiện tại của bảng danh mục.
SELECT * FROM `categories`;
Thực thi đoạn script trên trong MySQL Việc chạy lệnh Workbench trên cơ sở dữ liệu myflixdb cho chúng ta các kết quả sau.
| 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 |
Hiện có tám hàng, vì vậy ID được tạo tiếp theo sẽ là 9. Bây giờ chúng ta hãy chèn một danh mục mới vào bảng danh mục, chỉ cần cung cấp tên.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
Thực thi đoạn script trên đối với myflixdb trong MySQL bàn làm việc cho chúng ta kết quả như hình dưới đây.
| 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 |
Xin lưu ý rằng chúng tôi không cung cấp mã định danh danh mục. MySQL Nó được tạo tự động vì ID danh mục được định nghĩa là tự động tăng.
Nếu bạn muốn lấy id chèn cuối cùng được tạo bởi MySQL, bạn có thể sử dụng hàm LAST_INSERT_ID để làm điều đó. Tập lệnh hiển thị bên dưới lấy id cuối cùng được tạo.
SELECT LAST_INSERT_ID();
Việc thực thi đoạn mã trên sẽ trả về số tự động tăng cuối cùng được tạo ra bởi truy vấn INSERT. Kết quả được hiển thị bên dưới.
Mẹo: LAST_INSERT_ID() chỉ có phạm vi cho kết nối của bạn, vì vậy giá trị được tạo ra bởi thao tác chèn của người dùng khác sẽ không bao giờ được trả về cho bạn một cách nhầm lẫn.
Cách thiết lập hoặc đặt lại giá trị bắt đầu của AUTO_INCREMENT
Trình tự mặc định bắt đầu từ 1, nhưng đó không phải lúc nào cũng là điều mà một dự án cần. Số hóa đơn có thể phải tiếp tục từ hệ thống cũ và bảng kiểm tra thường phải được thiết lập lại. MySQL Nó hiển thị trực tiếp bộ đếm, vì vậy cả hai trường hợp đều được xử lý bằng một mệnh đề duy nhất. Hãy làm theo các bước sau để kiểm soát số bắt đầu.
- Đặt giá trị tại thời điểm tạo. Thêm mệnh đề AUTO_INCREMENT vào câu lệnh CREATE TABLE. Hàng đầu tiên được chèn vào sẽ nhận số đó thay vì số 1.
- Thay đổi giá trị trên một bảng hiện có. Sử dụng BẢNG BẢNG với cùng một điều khoản. MySQL Chỉ chấp nhận số mới nếu nó lớn hơn số định danh lớn nhất hiện đang được lưu trữ.
- Khôi phục lại bảng mà bạn đã xóa trống. Lệnh TRUNCATE TABLE xóa tất cả các hàng và trả về bộ đếm về 1 trong một thao tác duy nhất, điều mà lệnh DELETE đơn thuần không làm được.
- Xác nhận thay đổi. Chèn một hàng và đọc lại mã định danh bằng hàm LAST_INSERT_ID() trước khi dựa vào chuỗi mới.
-- 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`;
Kích thước bước cũng có thể được thay đổi bằng biến hệ thống auto_increment_increment, nhưng nó áp dụng cho toàn bộ máy chủ chứ không phải cho một bảng. Nó chủ yếu được sử dụng trong sao chép dữ liệu, nơi hai máy chủ không được tạo ra cùng một mã định danh.
Tại sao lại xuất hiện khoảng trống trong chuỗi AUTO_INCREMENT?
Sớm muộn gì bảng cũng sẽ hiển thị các mã định danh như 1, 2, 5, 6. Không có gì sai cả. Bộ đếm được thiết kế để đảm bảo tính duy nhất, chứ không phải để đảm bảo một dãy số liên tục, và nó không bao giờ trả về cùng một giá trị hai lần.
Khoảng trống xuất hiện vì những lý do sau.
- Các hàng đã bị xóa: Khi một hàng bị xóa khỏi bảng, ID tự động tăng của hàng đó sẽ không được sử dụng lại. MySQL tiếp tục tạo ra các số mới theo trình tự.
- Các giao dịch đã bị hoàn tác: Số thứ tự được giữ lại ngay khi thao tác chèn dữ liệu hoàn tất. Nếu giao dịch bị hủy bỏ, hàng dữ liệu sẽ biến mất nhưng số thứ tự đó đã được sử dụng.
- Các thao tác chèn không thành công: Một câu lệnh bị từ chối bởi ràng buộc UNIQUE vẫn có thể sử dụng một định danh trước khi nó thất bại.
- Chèn số lượng lớn: InnoDB có thể dành riêng một khối số cho việc chèn nhiều hàng và loại bỏ những số không được sử dụng.
Cố gắng lấp đầy những khoảng trống này là một sai lầm. Việc đánh số lại các hàng sẽ phá vỡ mọi khóa ngoại trỏ đến chúng, và bản thân giá trị đó không có ý nghĩa gì trong nghiệp vụ. Nếu báo cáo cần một danh sách liên tục, hãy tạo số thứ tự hàng trong truy vấn thay vì viết lại dữ liệu đã lưu trữ.
