MySQL: Почему auto_increment ограничивается только первичными ключами?

Я знаю, что MySQL ограничивает столбцы auto_increment первичными ключами. Почему это? Моя первая мысль заключается в том, что это ограничение производительности, поскольку, вероятно, есть какая-то таблица счетчиков, которая должна быть заблокирована, чтобы получить это значение.

Почему я не могу иметь несколько столбцов auto_increment в одной таблице?

Спасибо.

10 голосов | спросил Christopher Armstrong 23 J0000006Europe/Moscow 2011, 23:45:09

4 ответа


8

Почему вы хотите иметь столбец auto_increment, который не является основным ключом?

Если вы хотите, чтобы столбец был auto_increment, по определению вы не храните значимые данные в этом столбце. Единственный случай, когда хранение не имеющей смысла информации имеет смысл, - это особый случай, когда вы хотите иметь синтетический первичный ключ. В этом случае отсутствие информации является преимуществом, потому что нет риска, что кто-то когда-нибудь придет в будущем и захочет изменить данные, потому что какой-то атрибут какого-то объекта изменился.

Наличие нескольких столбцов auto_increment в одной таблице кажется еще более четким. Оба столбца будут иметь одинаковые данные - они генерируются одним и тем же алгоритмом и заселяются в то же время в конце концов. Я полагаю, вы могли бы придумать реализацию, где для них было бы немного не синхронизироваться, если бы было достаточно одновременных сеансов. Но я не могу представить, как это когда-либо будет полезно в приложении.

ответил Justin Cave 24 J0000006Europe/Moscow 2011, 01:03:56
8

Фактически атрибут AUTO_INCREMENT не ограничивается PRIMARY KEY (не более). Раньше это было в старых версиях - определенно 3.23 и, возможно, 4.0. Все еще руководство по MySQL для всех версий, начиная с 4.1, читается следующим образом

  

В таблице может быть только один столбец AUTO_INCREMENT, он должен быть проиндексирован и не может иметь значение DEFAULT.

Таким образом, у вас действительно может быть столбец AUTO_INCREMENT в таблице, который не является первичным ключом. Если это имеет смысл, это другая тема.

Следует также упомянуть, что столбец AUTO_INCREMENT всегда должен быть целым типом (технически тип с плавающей запятой также разрешен) и что он должен быть UNSIGNED. Тип SIGNED не только уничтожает половину ключевого пространства, но также может привести к огромным проблемам, если отрицательное значение вставляется случайно.

Наконец, MySQL 4.1 и более поздние версии определяют псевдоним типа SERIAL для BIGINT НЕ ПОДЛЕЖИТ НЕ УДАЛИТЬ АВТОМАТИЧЕСКУЮ УНИКАЛЬНУЮ.

ответил the XL 29 J0000006Europe/Moscow 2011, 03:26:39
4

Это интересный вопрос, потому что разные базы данных имеют уникальные подходы для обеспечения auto_increment.

MySQL : генерируется только один ключ auto_increment, чтобы однозначно идентифицировать строку в таблице. Почему-то объяснение объясняется не просто, а просто реализацией. В зависимости от типа данных значения auto_increment фиксируются длиной данных в байтах:

  • Макс. TINYINT - 127
  • Max UNSIGNED TINTINT - 255
  • Max INT - 2147483647
  • Max UNSIGNED INT - 4294967295

PostgreSQL

Внутренний тип данных используется для автоматического увеличения с 1 до 2 147 483 647. Большие диапазоны допускаются с использованием bigserial.

Oracle . Объект схемы, называемый SEQUENCE, может создавать новые числа, просто вызывая следующую функцию. PostgreSQL также имеет такой механизм.

Вот хороший URL-адрес, который показывает, как другие БД задают их: http: //www.w3schools .com /SQL /sql_autoincrement.asp

Теперь о вашем вопросе, если вы действительно хотите иметь несколько столбцов auto_increment в одной таблице, вам придется подражать этому.

Две причины, почему вы должны подражать этому:

  1. MySQL поддерживает только один столбец приращения для каждой таблицы, как и PostgreSQL, Oracle, SQL Server и MS Access.
  2. У MySQL нет объекта схемы SEQUENCE, такого как Oracle и PostgreSQL.

Как вы его эмулируете ???

Использование нескольких таблиц, которые имеют только один столбец auto_increment и сопоставление их с нужными столбцами в целевых таблицах. Вот пример:

Скопировать и вставить этот пример:

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;

Это создаст таблицу опросов для учителей. Я также создал пять эмуляторов последовательности, по одному на каждый день школьной недели. Каждый эмулятор последовательности работает, вставляя значение 0 в столбец val. Если эмулятор последовательности пуст, он начинается с val 0, nextval 1. Если нет, столбец nextval увеличивается. Затем вы можете получить следующий столбец из эмулятора последовательности.

Ниже приведены примеры из примера:

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>

Если вам действительно нужно несколько значений автоматического прироста в MySQL, это самый близкий способ имитировать его.

Дайте ему попробовать !!!

ОБНОВЛЕНИЕ 2011-06-23 21:05

Я только что заметил в своем примере, я не использую значение @pop.

На этот раз я заменил «pop_tue = pop_tue + 1» на «pop_tue = @pop» и повторил пример:

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>
ответил RolandoMySQLDBA 24 J0000006Europe/Moscow 2011, 04:38:48
0

Как говорит XL, он не ограничивается только первичными ключами. Это потенциальное ограничение, что вы можете иметь только один такой столбец для каждой таблицы, но лучшим решением является создание столько чисел, сколько вам нужно в другой таблице, а затем вставьте их туда, где вам нужно.

ответил sqlvogel 29 J0000006Europe/Moscow 2011, 12:25:36

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132