programming mysql

Tăng tự động mysql tăng 2

Tự động tăng cho phép một số duy nhất được tạo tự động khi một bản ghi mới được chèn vào bảng

Thường thì đây là trường khóa chính mà chúng tôi muốn được tạo tự động mỗi khi một bản ghi mới được chèn vào

Cú pháp cho MySQL

Câu lệnh SQL sau đây xác định cột "Personid" là trường khóa chính tăng tự động trong bảng "Persons"

TẠO BẢNG Người [
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar[255] NOT NULL,
    FirstName varchar[255],
    Age int,
    PRIMARY KEY [Personid]
];

MySQL sử dụng từ khóa AUTO_INCREMENT để thực hiện tính năng tự động tăng

Theo mặc định, giá trị bắt đầu cho AUTO_INCREMENT là 1 và giá trị này sẽ tăng thêm 1 cho mỗi bản ghi mới

Để chuỗi AUTO_INCREMENT bắt đầu với một giá trị khác, hãy sử dụng câu lệnh SQL sau

THAY ĐỔI BẢNG Người AUTO_INCREMENT=100;

Để chèn một bản ghi mới vào bảng "Persons", chúng ta KHÔNG phải chỉ định giá trị cho cột "Personid" [một giá trị duy nhất sẽ được thêm tự động]

CHÈN VÀO Người [Tên,Họ]
GIÁ TRỊ ['Lars','Monsen'];

Câu lệnh SQL trên sẽ chèn một bản ghi mới vào bảng "Persons". Cột "Personid" sẽ được gán số tiếp theo từ chuỗi seq_person. Cột "FirstName" sẽ được đặt thành "Lars" và cột "LastName" sẽ được đặt thành "Monsen"

Gần đây, chúng tôi tình cờ phát hiện ra một lỗi rất lạ khiến chúng tôi mất vài giờ để tìm ra. Chúng tôi đã bị mất dữ liệu trong một trong các bảng MySQL. Hãy đặt một số nền tảng cho toàn bộ thiết lập

Dự án

Chúng tôi có hai bảng MySQL. "hành động" và "hành động_archive". Cái đầu tiên đang giữ một số dữ liệu tạm thời liên tục được đẩy lên, cập nhật và xóa khỏi bảng này. Cái thứ hai là tấm gương của cái thứ nhất. Sự khác biệt là chúng tôi không xóa bất cứ thứ gì khỏi bảng lưu trữ. Đó là một kho lưu trữ 🙂 Tôi biết bạn đang nghĩ gì – điều này có thể được thiết kế theo cách khác giống như sử dụng xóa mềm. Vâng, tôi biết. Nhưng khi hệ thống đang được xây dựng, chúng tôi không biết mình sẽ cần kho lưu trữ. Nó được giới thiệu muộn hơn nhiều và vì mục đích kế thừa, chúng tôi đã giữ nguyên bảng "hành động" ban đầu. Hãy bỏ qua phần thiết kế và tập trung vào kiến trúc

Vấn đề

Một ngày đẹp trời [hoặc có thể trời mưa, tôi không thể nhớ], trong khi duyệt nội dung của bảng lưu trữ, chúng tôi nhận thấy rằng chúng tôi có các mục trùng lặp. Sau khi xem xét, chúng tôi phát hiện ra rằng một số bản ghi giống hệt nhau có các khóa chính khác nhau. Đây là điều không bao giờ nên xảy ra. Điều này dẫn chúng tôi đến kết luận rằng hệ thống đã tạo một mục mới trong bảng lưu trữ với ID của bản ghi gốc đã có trong bảng lưu trữ. Ok, điều này hơi phức tạp, vì vậy hãy để tôi làm rõ điều này. Giả sử bảng “actions_archive” thích hợp trông như thế này


|---------------------------------------------------|
|  id_actions_archive  |  id_action   |  some_data  |
|---------------------------------------------------|
|  100                 |  150         |  foo        |
|  101                 |  151         |  bar        |
|  102                 |  152         |  baz        |
|---------------------------------------------------|

Bây giờ chúng ta hãy xem một phiên bản bị hỏng của bảng này


|---------------------------------------------------|
|  id_actions_archive  |  id_action   |  some_data  |
|---------------------------------------------------|
|  100                 |  150         |  foo        |
|  101                 |  151         |  bar        |
[...]
|  332                 |  151         |  bar        |
[...]
|  637                 |  151         |  bar        |
|  638                 |  152         |  baz        |
|---------------------------------------------------|

Như bạn có thể thấy trong phiên bản sau của bảng, chúng tôi có các mục trùng lặp. Nó thậm chí còn tệ hơn vì có thể chúng tôi đã mất một số dữ liệu. Đó là bởi vì chúng tôi cũng cập nhật các bản ghi trong bảng lưu trữ khi chúng tôi cập nhật dữ liệu trong bảng "hành động" ban đầu. Chúng tôi sử dụng khóa “id_action” để cập nhật, vì vậy khi chúng tôi có một số mục nhập được liên kết với ID đó, tất cả chúng sẽ được cập nhật [thực ra là – bị ghi đè]. Điều đó không tốt

Tìm kiếm lỗi

Chúng tôi bắt đầu tìm kiếm vấn đề và không tìm thấy gì. Rõ ràng là rất khó chịu. Tất cả các phần chèn và cập nhật đều có khả năng chống đạn để chúng không thể làm hỏng dữ liệu. Chúng tôi thậm chí đã tạo một cơ chế ghi nhật ký để ghi lại thời điểm các bản sao đang được tạo. Nhưng điều đó không giúp chúng tôi chẩn đoán vấn đề

khám phá

Cuối cùng, sau rất nhiều lần động não, tìm kiếm, đọc và đập đầu vào tường, chúng tôi đã tìm ra lỗi. Đó không phải là dự án - đó là MySQL

Sự cố có thể được khắc phục bằng cách xóa dữ liệu khỏi bảng "hành động". Ví dụ, sau khi xóa 10 bản ghi cuối cùng khỏi bảng và khởi động lại MySQL, các bản ghi mới nhận được ID đã được chỉ định trước đó

Vì vậy, giả sử “id_action” cuối cùng là 500, sau đó chúng tôi đã xóa 100 bản ghi, khởi động lại MySQL và chèn một bản ghi mới vào bảng. ID của bản ghi mới được thêm hóa ra là 401

“Đợi đã, cái gì?. ” Vâng, đó chính xác là những gì chúng tôi đã nói. Nó hoàn toàn bất ngờ và kỳ lạ

Tôi tự hỏi: “Chuyện gì đang xảy ra vậy?’ ‘Có vấn đề gì vậy?

Vấn đề là sau khi khởi động lại MySQL, nó sẽ xác định giá trị auto_increment cho mỗi bảng bằng thuật toán này [chỉ áp dụng cho các bảng InnoDB]


SELECT MAX[ai_col] FROM t FOR UPDATE;

Đó là hành vi rất kỳ quái, ít nhất là đối với tôi. Một thứ quan trọng như giá trị auto_increment nên được lưu ở đâu đó. Điều trớ trêu ở đây là chúng ta đang nói về MySQL [DB, duh. ] và nó giữ giá trị auto_increment trong bộ nhớ… Khi MySQL được khởi động lại, nó sẽ lấy mục nhập cuối cùng từ một bảng và đặt giá trị auto_increment bằng với ID của bản ghi được tìm thấy + 1

Tài liệu thực sự nói rằng đây là một "tính năng" nhưng theo tôi, đây là một lỗi lớn. Bạn có thể đọc thêm về nó trong Hướng dẫn tham khảo MySQL. Biết được điều này, chúng tôi nhanh chóng tìm ra giải pháp cho vấn đề nhưng sẽ không ai biết chúng tôi đã rơi bao nhiêu nước mắt

Sự kết luận

Bạn có thể vấp phải những con bọ khiến bạn muốn nhảy ra ngoài qua cửa sổ. Đừng bỏ cuộc và luôn cố gắng tìm giải pháp. Nếu bạn không biết nguồn gốc của lỗi, hãy cố gắng làm tất cả những gì có thể để truy tìm nó. Đi xung quanh lỗi, cố gắng đóng đinh nó theo cách khác. Chúng tôi đã sử dụng cơ chế ghi nhật ký và ngay cả khi nó không giúp ích trực tiếp cho chúng tôi, nó đã loại bỏ một số lỗ hổng có thể xảy ra

Động não thực sự hữu ích. Ngay cả những ý tưởng không liên quan nhất [và có khả năng ngu ngốc] cũng có thể hữu ích. Lắng nghe mọi ý kiến và trao đổi với nhóm. Đôi khi một điều dẫn đến một điều khác và đột nhiên bạn tìm thấy giải pháp

Đừng hoàn toàn dựa vào phần mềm của bên thứ ba. PHP, MySQL và các ngôn ngữ khác cũng đầy lỗi. Ngay cả khi nhóm nhà phát triển nói rằng đó là một tính năng

Chúng ta có thể có 2 mức tăng tự động trong MySQL không?

Máy chủ MySQL đã cung cấp hai biến tăng tự động . auto_increment_increment và auto_increment_offset, có thể được sử dụng để tạo các giá trị gia tăng tự động khác nhau trên mỗi thành viên.

Chúng tôi có thể thay đổi giá trị gia tăng tự động trong MySQL không?

Trong MySQL, cú pháp để thay đổi giá trị bắt đầu cho cột AUTO_INCREMENT bằng cách sử dụng câu lệnh ALTER TABLE là. ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. Tên của bảng có giá trị AUTO_INCREMENT mà bạn muốn thay đổi.

Tại sao số lượng tự động tăng vọt nhiều hơn số lượng hàng được chèn?

Có thể có nhiều nguyên nhân. Kiểm tra xem giá trị auto_increment trên chính bảng đó có giá trị cao nhất tiếp theo hay không . Xin lưu ý rằng nếu bạn có các giao dịch mà bạn CHÈN một hàng và khôi phục giao dịch, giá trị auto_increment đó sẽ biến mất/bỏ qua.

Làm cách nào tôi có thể nhận được số gia tăng tự động tiếp theo trong MySQL?

MySQL có từ khóa AUTO_INCREMENT để thực hiện tăng tự động. Giá trị bắt đầu cho AUTO_INCREMENT là 1, là giá trị mặc định. Nó sẽ tăng thêm 1 cho mỗi bản ghi mới. Để lấy id tăng tự động tiếp theo trong MySQL, chúng ta có thể sử dụng hàm last_insert_id[] từ MySQL hoặc auto_increment với SELECT .