MySQL: Dlaczego auto_increment ogranicza się tylko do kluczy podstawowych?

Wiem, że MySQL ogranicza kolumny auto_increment do kluczy podstawowych. Dlaczego to? Moja pierwsza myśl jest taka, że ​​jest to ograniczenie wydajności, ponieważ prawdopodobnie istnieje gdzieś jakaś tabela liczników, która musi zostać zablokowana, aby uzyskać tę wartość.

Dlaczego nie mogę mieć wielu kolumn auto_increment w tej samej tabeli?

Dzięki.

Dlaczego chcesz mieć kolumnę auto_increment, która nie jest kluczem podstawowym?

Jeśli chcesz, aby kolumna była automatycznie zwiększana, z definicji nie przechowujesz znaczących danych w tej kolumnie. Jedynym przypadkiem, w którym sensowne jest przechowywanie nieistotnych informacji, jest szczególny przypadek, w którym chcesz mieć syntetyczny klucz podstawowy. W takim przypadku brak informacji jest zaletą, ponieważ nie ma ryzyka, że ​​ktoś kiedykolwiek przyjdzie w przyszłości i będzie chciał zmienić dane, ponieważ zmienił się atrybut niektórych podmiotów.

Posiadanie wielu kolumn auto_increment w tej samej tabeli wydaje się jeszcze dziwniejsze. Dwie kolumny miałyby te same dane - są generowane przez ten sam algorytm i mimo wszystko zapełniane w tym samym czasie. Przypuszczam, że możesz wymyślić implementację, w której możliwe jest nieznaczne zsynchronizowanie, jeśli jest wystarczająca liczba jednoczesnych sesji. Ale nie mogę sobie wyobrazić, jak byłoby to przydatne w aplikacji.

W rzeczywistości atrybut AUTO_INCREMENT nie jest ograniczony do KLUCZA PODSTAWOWEGO (już więcej). Tak było w starych wersjach - zdecydowanie 3.23 i prawdopodobnie 4.0. Ciągle instrukcja MySQL dla wszystkich wersji, ponieważ 4.1 brzmi następująco

W tabeli może znajdować się tylko jedna kolumna AUTO_INCREMENT, musi ona być indeksowana i nie może mieć wartości DOMYŚLNEJ.

Tak więc rzeczywiście możesz mieć kolumnę AUTO_INCREMENT w tabeli, która nie jest kluczem podstawowym. Jeśli to ma sens, to inny temat.

Powinienem również wspomnieć, że kolumna AUTO_INCREMENT powinna zawsze być liczbą całkowitą (technicznie dopuszczalny jest również typ zmiennoprzecinkowy) i że powinna być NIEPISYKOWANA. Typ SIGNED nie tylko marnuje połowę miejsca na klucz, ale może również prowadzić do ogromnych problemów, jeśli przypadkowo wstawiona zostanie wartość ujemna.

Wreszcie MySQL 4.1 i nowsze definiują alias typu SERIAL dla BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

To interesujące pytanie, ponieważ różne bazy danych mają unikalne podejście do zapewniania auto_increment.

MySQL : Generowany jest tylko jeden klucz auto_increment w celu jednoznacznej identyfikacji wiersza w tabeli. Nie ma wielu wyjaśnień za tym, ale tylko wdrożenie. W zależności od typu danych wartości auto_increment są ustalane przez długość typu danych w bajtach:

• Max TINYINT wynosi 127
• Maksymalna NIESPISYWANA TINTINT wynosi 255
• Max INT to 2147483647
• Max UNSIGNED INT to 4294967295

PostgreSQL

Wewnętrzny szereg typów danych jest używany do automatycznego przyrostu od 1 do 2 147 483 647. Większe zakresy są dozwolone przy użyciu bigserial.

Oracle : Obiekt schematu o nazwie SEQUENCE może tworzyć nowe liczby, po prostu przywołując funkcję nextval. PostgreSQL ma również taki mechanizm.

Oto ładny URL, który podaje, w jaki sposób określają je inne bazy danych: http://www.w3schools.com/sql/sql_autoincrement.asp

Jeśli chodzi o twoje pytanie, jeśli naprawdę chcesz mieć wiele kolumn auto_increment w jednej tabeli, musisz to naśladować.

Dwa powody, dla których musisz to naśladować:

1. MySQL obsługuje tylko jedną kolumnę przyrostową na tabelę, podobnie jak PostgreSQL, Oracle, SQL Server i MS Access.
2. MySQL nie ma obiektu schematu SEKWENCJA, takiego jak Oracle i PostgreSQL.

Jak byś to naśladował ???

Używanie wielu tabel, które mają tylko jedną kolumnę auto_increment i mapowanie ich na pożądane kolumny w tabelach docelowych. Oto przykład:

Skopiuj i wklej ten przykład:

use test
DROP TABLE IF EXISTS teacher_popquizzes;
CREATE TABLE teacher_popquizzes
(
    teacher varchar(20) not null,
    class varchar(20) not null,
    pop_mon INT NOT NULL DEFAULT 0,
    pop_tue INT NOT NULL DEFAULT 0,
    pop_wed INT NOT NULL DEFAULT 0,
    pop_thu INT NOT NULL DEFAULT 0,
    pop_fri INT NOT NULL DEFAULT 0,
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
INSERT INTO teacher_popquizzes (teacher,class) VALUES
('mr jackson','literature'),
('mrs andrews','history'),
('miss carroll','spelling');
DROP TABLE IF EXISTS mon_seq;
DROP TABLE IF EXISTS tue_seq;
DROP TABLE IF EXISTS wed_seq;
DROP TABLE IF EXISTS thu_seq;
DROP TABLE IF EXISTS fri_seq;
CREATE TABLE mon_seq
(
    val INT NOT NULL DEFAULT 0,
    nextval INT NOT NULL DEFAULT 1,
    PRIMARY KEY (val)
);
CREATE TABLE tue_seq LIKE mon_seq;
CREATE TABLE wed_seq LIKE mon_seq;
CREATE TABLE thu_seq LIKE mon_seq;
CREATE TABLE fri_seq LIKE mon_seq;
BEGIN;
INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
SELECT nextval INTO @pop FROM mon_seq;
UPDATE teacher_popquizzes SET pop_tue = pop_tue + 1 WHERE id = 2;
COMMIT;
BEGIN;
INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
SELECT nextval INTO @pop FROM tue_seq;
UPDATE teacher_popquizzes SET pop_tue = pop_tue + 1 WHERE id = 1;
COMMIT;
BEGIN;
INSERT INTO wed_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
SELECT nextval INTO @pop FROM wed_seq;
UPDATE teacher_popquizzes SET pop_wed = pop_wed + 1 WHERE id = 2;
COMMIT;
SELECT * FROM teacher_popquizzes;

Spowoduje to utworzenie tabeli popowych quizów dla nauczycieli. Stworzyłem również pięć emulatorów sekwencji, po jednym na każdy dzień tygodnia szkolnego. Każdy emulator sekwencji działa, wstawiając wartość 0 w kolumnie val. Jeśli emulator sekwencji jest pusty, zaczyna się od wartości 0, nextval 1. Jeśli nie, kolumna nextval jest zwiększana. Następnie możesz pobrać kolumnę nextval z emulatora sekwencji.

Oto przykładowe wyniki z przykładu:

mysql> CREATE TABLE teacher_popquizzes
    -> (
    ->     teacher varchar(20) not null,
    ->     class varchar(20) not null,
    ->     pop_mon INT NOT NULL DEFAULT 0,
    ->     pop_tue INT NOT NULL DEFAULT 0,
    ->     pop_wed INT NOT NULL DEFAULT 0,
    ->     pop_thu INT NOT NULL DEFAULT 0,
    ->     pop_fri INT NOT NULL DEFAULT 0,
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO teacher_popquizzes (teacher,class) VALUES
    -> ('mr jackson','literature'),
    -> ('mrs andrews','history'),
    -> ('miss carroll','spelling');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS mon_seq;
Query OK, 0 rows affected (0.06 sec)

mysql> DROP TABLE IF EXISTS tue_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS wed_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS thu_seq;
Query OK, 0 rows affected (0.05 sec)

mysql> DROP TABLE IF EXISTS fri_seq;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE mon_seq
    -> (
    ->     val INT NOT NULL DEFAULT 0,
    ->     nextval INT NOT NULL DEFAULT 1,
    ->     PRIMARY KEY (val)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE tue_seq LIKE mon_seq;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE wed_seq LIKE mon_seq;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE thu_seq LIKE mon_seq;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE fri_seq LIKE mon_seq;
Query OK, 0 rows affected (0.14 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT nextval INTO @pop FROM mon_seq;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_tue = pop_tue + 1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT nextval INTO @pop FROM tue_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_tue = pop_tue + 1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO wed_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT nextval INTO @pop FROM wed_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_wed = pop_wed + 1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM teacher_popquizzes;
+--------------+------------+---------+---------+---------+---------+---------+----+
| teacher      | class      | pop_mon | pop_tue | pop_wed | pop_thu | pop_fri | id |
+--------------+------------+---------+---------+---------+---------+---------+----+
| mr jackson   | literature |       0 |       1 |       0 |       0 |       0 |  1 |
| mrs andrews  | history    |       0 |       1 |       1 |       0 |       0 |  2 |
| miss carroll | spelling   |       0 |       0 |       0 |       0 |       0 |  3 |
+--------------+------------+---------+---------+---------+---------+---------+----+
3 rows in set (0.00 sec)

mysql>

Jeśli naprawdę potrzebujesz wielu wartości automatycznego przyrostu w MySQL, jest to najbliższy sposób na jego emulację.

Spróbuj !!!

AKTUALIZACJA 23.06.2011 21:05

Właśnie zauważyłem w moim przykładzie, że nie używam wartości @pop.

Tym razem zamieniłem „pop_tue = pop_tue + 1” na „pop_tue = @pop” i powtórzyłem przykład:

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS teacher_popquizzes;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE teacher_popquizzes
    -> (
    ->     teacher varchar(20) not null,
    ->     class varchar(20) not null,
    ->     pop_mon INT NOT NULL DEFAULT 0,
    ->     pop_tue INT NOT NULL DEFAULT 0,
    ->     pop_wed INT NOT NULL DEFAULT 0,
    ->     pop_thu INT NOT NULL DEFAULT 0,
    ->     pop_fri INT NOT NULL DEFAULT 0,
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO teacher_popquizzes (teacher,class) VALUES
    -> ('mr jackson','literature'),
    -> ('mrs andrews','history'),
    -> ('miss carroll','spelling');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS mon_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS tue_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS wed_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS thu_seq;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS fri_seq;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE mon_seq
    -> (
    ->     val INT NOT NULL DEFAULT 0,
    ->     nextval INT NOT NULL DEFAULT 1,
    ->     PRIMARY KEY (val)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE tue_seq LIKE mon_seq;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE wed_seq LIKE mon_seq;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE thu_seq LIKE mon_seq;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE fri_seq LIKE mon_seq;
Query OK, 0 rows affected (0.08 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;

Query OK, 1 row affected (0.01 sec)

mysql> SELECT nextval INTO @pop FROM tue_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_tue = @pop WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tue_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;

Query OK, 2 rows affected (0.00 sec)

mysql> SELECT nextval INTO @pop FROM tue_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_tue = @pop WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO wed_seq (val) VALUES (0) ON DUPLICATE KEY UPDATE nextval = nextval + 1;

Query OK, 1 row affected (0.01 sec)

mysql> SELECT nextval INTO @pop FROM wed_seq;
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE teacher_popquizzes SET pop_wed = @pop WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM teacher_popquizzes;
+--------------+------------+---------+---------+---------+---------+---------+----+
| teacher      | class      | pop_mon | pop_tue | pop_wed | pop_thu | pop_fri | id |
+--------------+------------+---------+---------+---------+---------+---------+----+
| mr jackson   | literature |       0 |       2 |       0 |       0 |       0 |  1 |
| mrs andrews  | history    |       0 |       1 |       1 |       0 |       0 |  2 |
| miss carroll | spelling   |       0 |       0 |       0 |       0 |       0 |  3 |
+--------------+------------+---------+---------+---------+---------+---------+----+
3 rows in set (0.00 sec)

mysql>

Jak mówi XL, nie ogranicza się to tylko do kluczy podstawowych. To potencjalne ograniczenie, że możesz mieć tylko jedną taką kolumnę na tabelę, ale najlepszym rozwiązaniem jest wygenerowanie tylu liczb, których potrzebujesz w innej tabeli, a następnie wstawienie ich tam, gdzie jest to konieczne.