Làm cách nào để đặt lại cột nhận dạng trong MySQL?

Tự động tăng là một tính năng hữu ích được cung cấp bởi MySQL. Nó luôn tạo ra một danh tính duy nhất cho mỗi hàng mới được chèn vào bảng. Nói chung, chúng tôi sử dụng thuộc tính tăng tự động cho trường khóa chính trong bảng. Mỗi lần chúng ta chèn các bản ghi mới vào một bảng, MySQL sẽ tự động tăng giá trị vào cột tăng tự động

Ví dụ: chúng tôi có một bảng đã chứa mười hàng. Tiếp theo, nếu chúng ta thêm một hàng mới mà không đưa giá trị vào cột tăng tự động, MySQL sẽ tự động thêm một hàng mới với id=11

Đôi khi, chúng ta cần xóa các hàng này và đặt lại cột tăng tự động để khi chúng ta thực hiện lần chèn tiếp theo vào bảng, danh tính của bản ghi đầu tiên sẽ có giá trị khóa chính là 1

MySQL cho phép nhiều cách khác nhau để đặt lại giá trị cột tăng tự động. Những cách này là

1. Sử dụng câu lệnh ALTER TABLE
2. Sử dụng câu lệnh TRUNCATE TABLE
3. Sử dụng một cặp câu lệnh DROP TABLE và CREATE TABLE

Đầu tiên, chúng ta sẽ tạo một bảng có tên là "Động vật" và gán cột id với thuộc tính tự động tăng. Xem tuyên bố dưới đây

Tiếp theo, chúng ta sẽ chèn một số dữ liệu mẫu vào bảng "Động vật" như sau

Bây giờ, chúng tôi sẽ thực hiện câu lệnh dưới đây để xác minh thao tác chèn

Chúng ta sẽ nhận được đầu ra dưới đây. Trong câu lệnh chèn, chúng tôi chưa chỉ định bất kỳ giá trị nào cho cột tăng tự động. Nhưng chúng ta có thể quan sát thấy ở đầu ra rằng MySQL tự động tạo một số duy nhất theo thứ tự cho trường này

Hãy để chúng tôi lấy bảng này để minh họa các phương pháp đặt lại giá trị cột tăng tự động một cách chi tiết

Sử dụng câu lệnh ALTER TABLE

Câu lệnh ALTER TABLE được sử dụng để thay đổi tên của bảng hoặc bất kỳ trường nào của bảng. Nó cũng được sử dụng để thêm, xóa hoặc đặt lại một cột hiện có trong bảng. MySQL cũng cho phép câu lệnh này đặt lại giá trị cột tăng tự động bất cứ khi nào chúng tôi muốn

cú pháp

Sau đây là cú pháp của câu lệnh ALTER TABLE để đặt lại giá trị cột tự động tăng

Trong cú pháp này, trước tiên chúng tôi đã chỉ định tên của bảng sau mệnh đề ALTER TABLE. Tiếp theo, chúng tôi chỉ định giá trị mà chúng tôi muốn đặt lại trong biểu thức là AUTO_INCREMENT = value

Cần lưu ý rằng MySQL không cho phép đặt lại giá trị nhỏ hơn hoặc bằng giá trị đã được sử dụng. Ví dụ: nếu chúng tôi đang sử dụng InnoDB, giá trị không được nhỏ hơn hoặc bằng giá trị tối đa hiện tại của cột tăng tự động

Đối với MyISAM, nếu giá trị đã chỉ định nhỏ hơn hoặc bằng giá trị tối đa của cột auto_increment, thì giá trị đó sẽ được đặt lại về giá trị tối đa hiện tại cộng với một

Đối với InnoDB, nếu giá trị được chỉ định nhỏ hơn giá trị tối đa hiện tại của cột auto_increment, MySQL sẽ không phát sinh bất kỳ lỗi nào và trình tự hiện tại cũng không thay đổi

Hãy xóa hàng thấp nhất trong bảng với id = 6 như bên dưới

Nếu chúng ta thêm một bản ghi mới, MySQL sẽ gán bản ghi này với id = 7. Nhưng nếu chúng ta muốn đặt lại cột này về giá trị cuối cùng, chúng ta có thể sử dụng câu lệnh ALTER TABLE như sau

Nếu chúng ta chèn các bản ghi mới vào bảng này, MySQL sẽ bắt đầu tạo id hàng với 6. Thực hiện truy vấn dưới đây

Cuối cùng, truy vấn dữ liệu trong bảng để xem hiệu quả. Đầu ra bên dưới giải thích rõ ràng hơn khi chúng ta có sáu hàng với giá trị gia tăng tự động cuối cùng là 6 thay vì 7

Sử dụng câu lệnh TRUNCATE TABLE

Câu lệnh TRUNCATE TABLE trong MySQL xóa hoàn toàn dữ liệu của bảng mà không xóa cấu trúc của bảng và luôn đặt lại giá trị cột tăng tự động về 0

cú pháp

Cú pháp sau minh họa câu lệnh TRUNCATE TABLE để đặt lại giá trị cột tự động tăng

Cần lưu ý rằng chúng ta không thể khôi phục dữ liệu đã xóa khỏi bảng sau khi thực hiện câu lệnh TRUNCATE TABLE

Sử dụng câu lệnh DROP TABLE và CREATE TABLE

Chúng ta cũng có thể đặt lại giá trị cột tăng tự động bằng cách sử dụng một cặp câu lệnh DROP TABLE và CREATE TABLE. Phương pháp này xóa vĩnh viễn các bản ghi hoàn chỉnh khỏi bảng

Tương tự như truy vấn TRUNCATE TABLE, cặp câu lệnh DROP TABLE và CREATE TABLE trước tiên xóa bảng rồi tạo lại bảng. Do đó, MySQL đặt lại giá trị cột tăng tự động thành 0

Cột nhận dạng là một tính năng trong SQL Server cho phép bạn tự động tăng giá trị của cột. Điều này hữu ích cho các khóa chính, khi bạn không quan tâm số đó là gì, miễn là nó là duy nhất

Bạn có thể chỉ định từ IDENTITY làm thuộc tính sau kiểu dữ liệu khi tạo hoặc thay đổi bảng

Ví dụ

CREATE TABLE product [
  product_id INT IDENTITY,
  product_name VARCHAR[200],
  price INT
];

Câu lệnh này sẽ tạo một bảng mới có tên là sản phẩm

Product_id có từ IDENTITY sau nó, nghĩa là bản ghi mới sẽ có giá trị được tạo tự động

Hãy xem một ví dụ về điều này

INSERT INTO product [product_name, price]
VALUES ['Chair', 100];

Chúng ta có thể kiểm tra giá trị đã được chèn bằng cách chọn dữ liệu từ bảng

SELECT product_id, product_name, price
FROM product;

product_idproduct_nameprice1Chair100

Chúng ta có thể chèn hàng thứ hai vào bảng để xem cột nhận dạng product_id sẽ được đặt thành gì

INSERT INTO product [product_name, price]
VALUES ['Desk', 250];

Để xem các bản ghi trong bảng, chúng ta có thể chọn từ nó

SELECT product_id, product_name, price
FROM product;

product_idproduct_nameprice1Ghế1002Desk250

Không chỉ định product_id trong câu lệnh INSERT, tính năng IDENTITY đã tạo một số và điền vào hàng. Một số mới được tạo cho mỗi hàng

 

Tại sao Đặt lại Cột Nhận dạng?

Vậy vấn đề là gì?

Hãy xóa một bản ghi và chèn một bản ghi mới

DELETE FROM product
WHERE product_id = 2;

INSERT INTO product [product_name, price]
VALUES ['Large Desk', 300];

Khi chúng tôi xóa một hàng và chèn một hàng mới, đây là bảng của chúng tôi trông như thế nào

SELECT product_id, product_name, price
FROM product;

product_idproduct_namegiá1Ghế1003Bàn lớn300

Chúng ta có thể thấy rằng hàng mới có product_id là 3 chứ không phải 2

Giá trị ID của chúng tôi không theo thứ tự

Vì vậy, nếu bạn muốn chúng theo thứ tự, bạn sẽ phải đặt lại cột nhận dạng

 

Giá trị bản sắc không quan trọng

Nếu bạn ở đây vì bạn muốn các giá trị nhận dạng khóa chính của mình theo thứ tự số mà không có bất kỳ khoảng trống nào, thì tôi khuyên bạn không cần thiết

Giá trị khóa chính sẽ không có ý nghĩa đối với bất kỳ người dùng hoặc ứng dụng nào bên ngoài cơ sở dữ liệu. Mục đích duy nhất của nó là xác định duy nhất một hàng và do đó được sử dụng để liên kết với các hàng khác trong các bảng khác

Cơ sở dữ liệu không quan trọng nếu các giá trị theo thứ tự 1, 2, 3 hoặc nếu thiếu các giá trị như 1, 4, 8. Miễn là chúng là duy nhất, cơ sở dữ liệu sẽ vẫn hoạt động theo cùng một cách

Nếu ai đó nhìn vào đầu ra của cơ sở dữ liệu [e. g. một báo cáo có ID] và tự hỏi tại sao chúng không theo thứ tự hoặc có khoảng trống, sau đó

• bạn có thể giải thích với họ rằng ID vẫn ổn nếu có khoảng trống
• nếu họ muốn các số không có khoảng trống, thay vào đó, bạn có thể thay đổi truy vấn để tạo số hàng

Nếu ai đó đang sử dụng các giá trị nhận dạng để xác định có bao nhiêu hàng trong một bảng và cột đang "bỏ qua" các giá trị như thế này, thì họ có thể nghĩ rằng có nhiều hàng trong bảng hơn thực tế.

Tuy nhiên, cách đúng để đếm bản ghi là sử dụng hàm COUNT, không phải bằng cách xem cột nhận dạng dưới dạng số hàng

Vì vậy, tóm lại, giá trị của cột nhận dạng là bao nhiêu không quan trọng. Miễn là nó độc đáo là được

Mọi người không cần nhìn thấy giá trị

Phải nói rằng, nếu bạn thực sự muốn đặt lại các giá trị nhận dạng [e. g. cho bài kiểm tra bạn đang làm hoặc cho một dự án ở trường đại học], thì bạn có thể thực hiện điều đó trong SQL Server

 

Kiểm tra giá trị hiện tại bằng thủ tục DBCC CHECKIDENT

Bạn có thể chạy thủ tục DBCC CHECKIDENT trong SQL Server để xem giá trị hiện tại của cột nhận dạng là gì. Bạn chỉ cần chỉ định tên của bảng làm tham số

Đây là một ví dụ

DBCC CHECKIDENT['product'];

Điều này sẽ cho bạn thấy giá trị hiện tại của cột nhận dạng của bảng này

________số 8

Giá trị của 3 là giá trị gần đây nhất của cột danh tính. Hàng tiếp theo được chèn sẽ có giá trị là 4

Vì vậy, làm thế nào để chúng ta thiết lập lại giá trị?

 

Đặt lại giá trị nhận dạng bằng thủ tục DBCC CHECKIDENT

Nếu bạn muốn đặt lại cột nhận dạng trong SQL Server, bạn có thể sử dụng quy trình DBCC CHECKIDENT với các tham số bổ sung

DBCC CHECKIDENT ['table_name', RESEED, new_value];

Đặt lại bảng sản xuất của chúng tôi để sử dụng giá trị 1 được thực hiện bằng lệnh này

INSERT INTO product [product_name, price]
VALUES ['Chair', 100];

0

Tuy nhiên, trong bảng hiện có các bản ghi nên nếu chúng ta đặt lại và chèn một bản ghi mới thì sẽ xảy ra lỗi

Vậy, chúng ta làm gì?

1. Xóa tất cả dữ liệu khỏi bảng
2. Đặt lại danh tính
3. Chèn lại tất cả dữ liệu

kịch bản của chúng tôi sẽ trông như thế này

INSERT INTO product [product_name, price]
VALUES ['Chair', 100];

1

Điều này sẽ đặt lại giá trị nhận dạng thành 0, khiến hai hàng được chèn có ID là 1 và 2

Kết quả là

product_idproduct_nameprice1Chair1002Large Desk300

Vì vậy, đó là cách bạn có thể đặt lại cột nhận dạng trong SQL Server

 

Đặt lại giá trị danh tính và giữ dữ liệu bảng

Nếu bạn có một cái bàn lớn thì sao?

Một cách bạn có thể làm là lưu trữ các giá trị trong một bảng khác. Quá trình sẽ là

1. Tạo một bảng mới bằng cách sử dụng các giá trị từ bảng thực
2. Xóa dữ liệu khỏi bảng thực
3. Đặt lại danh tính
4. Chèn lại từ bảng mới

Kịch bản của bạn có thể trông như thế này

INSERT INTO product [product_name, price]
VALUES ['Chair', 100];

2

Có một vài điều cần lưu ý ở đây. Bảng product_backup lưu trữ product_id, có các khoảng trống, vì vậy nó có thể được sử dụng theo thứ tự sau này trong tập lệnh. Điều này rất quan trọng nếu bạn muốn dữ liệu theo cùng một thứ tự. Điều này có thể không thực sự quan trọng vì bạn chỉ có thể sắp xếp dữ liệu của mình trong bất kỳ truy vấn nào trong tương lai, nhưng sẽ hữu ích nếu đó là điều bạn muốn

Ngoài ra, trong câu lệnh INSERT, bạn chọn từ bảng product_backup, nhưng bạn không chèn product_id từ bảng đó. Cột nhận dạng được tạo

Dữ liệu của bạn sẽ trông giống như ví dụ trước đó

product_idproduct_nameprice1Chair1002Large Desk300

 

Đặt lại giá trị nhận dạng. Xóa so với cắt bớt

Một vấn đề khi sử dụng phương pháp này là giá trị mà quy trình CHECKIDENT yêu cầu sẽ khác nhau tùy thuộc vào việc bạn CẮT hay XÓA

Bài viết này ở đây chi tiết một số thử nghiệm để giải thích điều này

Nói ngắn gọn

• Khi bạn sử dụng DELETE để xóa tất cả các hàng trong bảng, giá trị được gán tiếp theo cho cột nhận dạng là giá trị được thêm lại mới + gia số hiện tại
• Khi bạn sử dụng TRUNCATE, giá trị được gán tiếp theo cho cột nhận dạng là giá trị gốc ban đầu

Hãy xem bài viết trên để biết thêm chi tiết về các thử nghiệm đã được chạy

 

Phần kết luận

Sử dụng cột nhận dạng cho khóa chính tự động tạo là một tính năng tuyệt vời. Các giá trị của cột nhận dạng không quan trọng vì vậy sẽ không thành vấn đề nếu chúng không liên tục. Nhưng nếu bạn thực sự cần làm cho chúng liên tục, bạn có thể đặt lại chúng mà không làm rơi và tạo lại bảng bằng cách sử dụng các bước trong bài viết này

Làm cách nào để đặt lại id trong cơ sở dữ liệu MySQL?

Làm cách nào để đặt lại id trong MySQL sau khi xóa?

Tại sao cắt ngắn đặt lại giá trị Auto_increment?

Làm cách nào để đặt giá trị mặc định cho Auto_increment trong MySQL?