172 lines
4.5 KiB
Plaintext
172 lines
4.5 KiB
Plaintext
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(347),(33101),(123),(45),(6);
|
|
ALTER TABLE t1 ADD PRIMARY KEY(a);
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
|
|
LOCK=NONE;
|
|
ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED.
|
|
ALTER TABLE t1 ADD id INT AUTO_INCREMENT;
|
|
ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
|
|
ALTER TABLE t1 ADD id INT AUTO_INCREMENT, ADD INDEX(a, id);
|
|
ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
|
|
ALTER TABLE t1 ADD id INT NOT NULL, ADD INDEX(id, a);
|
|
SELECT * FROM t1;
|
|
a id
|
|
6 0
|
|
45 0
|
|
123 0
|
|
347 0
|
|
33101 0
|
|
SET AUTO_INCREMENT_INCREMENT = 5, AUTO_INCREMENT_OFFSET = 30;
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
|
|
DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=COPY;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id`),
|
|
KEY `id` (`id`,`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES(7,0);
|
|
SELECT * FROM t1;
|
|
a id
|
|
6 45
|
|
45 50
|
|
123 55
|
|
347 60
|
|
33101 65
|
|
7 70
|
|
ROLLBACK;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id`),
|
|
KEY `id` (`id`,`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
|
|
DROP COLUMN id, AUTO_INCREMENT = 42, LOCK=NONE;
|
|
ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED.
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
|
|
DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE;
|
|
SELECT * FROM t1;
|
|
a id
|
|
6 45
|
|
45 50
|
|
123 55
|
|
347 60
|
|
33101 65
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id`),
|
|
KEY `id` (`id`,`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
INSERT INTO t1 SET a=123;
|
|
INSERT INTO t1 VALUES(-123,-45);
|
|
ALTER TABLE t1 AUTO_INCREMENT = 75;
|
|
INSERT INTO t1 SET a=123;
|
|
SELECT * FROM t1;
|
|
a id
|
|
-123 -45
|
|
6 45
|
|
45 50
|
|
123 55
|
|
347 60
|
|
33101 65
|
|
123 70
|
|
123 75
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id`),
|
|
KEY `id` (`id`,`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(347),(33101),(123),(45),(6);
|
|
ALTER TABLE t1 ADD PRIMARY KEY(a);
|
|
ALTER TABLE t1 ADD id INT NOT NULL, ADD INDEX(id, a);
|
|
SELECT * FROM t1;
|
|
a id
|
|
6 0
|
|
45 0
|
|
123 0
|
|
347 0
|
|
33101 0
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
|
|
DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id`),
|
|
KEY `id` (`id`,`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES(7,0);
|
|
SELECT * FROM t1;
|
|
a id
|
|
6 45
|
|
45 50
|
|
123 55
|
|
347 60
|
|
33101 65
|
|
7 70
|
|
ROLLBACK;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id`),
|
|
KEY `id` (`id`,`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY,
|
|
DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=COPY;
|
|
SELECT * FROM t1;
|
|
a id
|
|
6 45
|
|
45 50
|
|
123 55
|
|
347 60
|
|
33101 65
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id`),
|
|
KEY `id` (`id`,`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
INSERT INTO t1 SET a=123;
|
|
INSERT INTO t1 VALUES(-123,-45);
|
|
ALTER TABLE t1 AUTO_INCREMENT = 75;
|
|
INSERT INTO t1 SET a=123;
|
|
SELECT * FROM t1;
|
|
a id
|
|
-123 -45
|
|
6 45
|
|
45 50
|
|
123 55
|
|
347 60
|
|
33101 65
|
|
123 70
|
|
123 75
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id`),
|
|
KEY `id` (`id`,`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|