drop table if exists t1; CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (9223372036854775807, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 9223372036854775807 NULL DROP TABLE t1; CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (127, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 127 NULL DROP TABLE t1; CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (255, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 255 NULL DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (32767, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 32767 NULL DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (65535, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 65535 NULL DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (8388607, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 8388607 NULL DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (16777215, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 16777215 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (2147483647, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 2147483647 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (4294967295, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 4294967295 NULL DROP TABLE t1; CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (9223372036854775807, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 9223372036854775807 NULL DROP TABLE t1; CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (18446744073709551615, null); INSERT INTO t1 (c2) VALUES ('innodb'); Got one of the listed errors SELECT * FROM t1; c1 c2 18446744073709551615 NULL DROP TABLE t1; CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t1 VALUES (NULL), (NULL), (NULL); SELECT c1 FROM t1; c1 1 2 3 4 5 6 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 TRUNCATE TABLE t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t1 VALUES (NULL), (NULL), (NULL); SELECT c1 FROM t1; c1 1 2 3 4 5 6 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t1 VALUES (NULL), (NULL), (NULL); SELECT c1 FROM t1; c1 1 2 3 4 5 6 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 DELETE FROM t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t1 VALUES (NULL), (NULL), (NULL); SELECT c1 FROM t1; c1 1 2 3 7 8 9 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL, 1); DELETE FROM t1 WHERE c1 = 1; INSERT INTO t1 VALUES (2,1); INSERT INTO t1 VALUES (NULL,8); SELECT * FROM t1; c1 c2 2 1 3 8 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL, 1); DELETE FROM t1 WHERE c1 = 1; INSERT INTO t1 VALUES (2,1), (NULL, 8); INSERT INTO t1 VALUES (NULL,9); SELECT * FROM t1; c1 c2 2 1 3 8 5 9 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 100 auto_increment_offset 10 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL),(5),(NULL); INSERT INTO t1 VALUES (250),(NULL); SELECT * FROM t1; c1 5 10 110 250 310 INSERT INTO t1 VALUES (1000); SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL),(NULL); SELECT * FROM t1; c1 5 10 110 250 310 400 410 1000 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(0); SELECT * FROM t1; c1 1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; INSERT INTO t1 VALUES (-1), (NULL),(2),(NULL); INSERT INTO t1 VALUES (250),(NULL); SELECT * FROM t1; c1 -1 1 2 10 110 250 410 SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL),(NULL); Got one of the listed errors SELECT * FROM t1; c1 -1 1 2 10 110 250 410 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(-1); SELECT * FROM t1; c1 -1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 100 auto_increment_offset 10 INSERT INTO t1 VALUES (-2), (NULL),(2),(NULL); INSERT INTO t1 VALUES (250),(NULL); SELECT * FROM t1; c1 -2 -1 1 2 10 250 310 INSERT INTO t1 VALUES (1000); SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL),(NULL); SELECT * FROM t1; c1 -2 -1 1 2 10 250 310 400 410 1000 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(-1); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 100 auto_increment_offset 10 INSERT INTO t1 VALUES (-2); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (NULL); INSERT INTO t1 VALUES (250); INSERT INTO t1 VALUES (NULL); SELECT * FROM t1; c1 1 2 10 110 210 250 310 INSERT INTO t1 VALUES (1000); SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES(NULL); SELECT * FROM t1; c1 1 2 10 110 210 250 310 400 1000 1010 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(-1); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 SELECT * FROM t1; c1 1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 100 auto_increment_offset 10 INSERT INTO t1 VALUES (-2),(NULL),(2),(NULL); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (250),(NULL); SELECT * FROM t1; c1 1 2 10 110 210 250 410 INSERT INTO t1 VALUES (1000); SET @@INSERT_ID=400; INSERT INTO t1 VALUES(NULL),(NULL); Got one of the listed errors SELECT * FROM t1; c1 1 2 10 110 210 250 410 1000 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES (9223372036854775794); SELECT * FROM t1; c1 1 9223372036854775794 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 2 auto_increment_offset 10 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); SELECT * FROM t1; c1 1 9223372036854775794 9223372036854775796 9223372036854775798 9223372036854775800 9223372036854775802 9223372036854775804 9223372036854775806 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES (18446744073709551603); SELECT * FROM t1; c1 1 18446744073709551603 SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 2 auto_increment_offset 10 INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); SELECT * FROM t1; c1 1 18446744073709551603 18446744073709551604 18446744073709551606 18446744073709551608 18446744073709551610 18446744073709551612 18446744073709551614 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES (18446744073709551603); SELECT * FROM t1; c1 1 18446744073709551603 SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 5 auto_increment_offset 7 INSERT INTO t1 VALUES (NULL),(NULL); SELECT * FROM t1; c1 1 18446744073709551603 18446744073709551607 18446744073709551612 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES(-9223372036854775806); INSERT INTO t1 VALUES(-9223372036854775807); INSERT INTO t1 VALUES(-9223372036854775808); SELECT * FROM t1; c1 -9223372036854775808 -9223372036854775807 -9223372036854775806 1 SET @@SESSION.AUTO_INCREMENT_INCREMENT=3, @@SESSION.AUTO_INCREMENT_OFFSET=3; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 3 auto_increment_offset 3 INSERT INTO t1 VALUES (NULL),(NULL), (NULL); SELECT * FROM t1; c1 -9223372036854775808 -9223372036854775807 -9223372036854775806 1 3 6 9 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES (18446744073709551610); SELECT * FROM t1; c1 1 18446744073709551610 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; Warnings: Warning 1292 Truncated incorrect auto_increment_increment value: '1152921504606846976' Warning 1292 Truncated incorrect auto_increment_offset value: '1152921504606846976' SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 65535 auto_increment_offset 65535 INSERT INTO t1 VALUES (NULL); SELECT * FROM t1; c1 1 18446744073709551610 18446744073709551615 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SET @@INSERT_ID=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL, 1); INSERT INTO t1 VALUES(NULL, 2); SELECT * FROM t1; c1 c2 1 1 2 2 ALTER TABLE t1 CHANGE c1 c1 SERIAL; SELECT * FROM t1; c1 c2 1 1 2 2 INSERT INTO t1 VALUES(NULL, 3); INSERT INTO t1 VALUES(NULL, 4); SELECT * FROM t1; c1 c2 1 1 2 2 3 3 4 4 DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB; INSERT INTO t1 VALUES(NULL, 1); INSERT INTO t1 VALUES(NULL, 2); SELECT * FROM t1; c1 c2 1 1 2 2 ALTER TABLE t1 CHANGE c1 c1 SERIAL; SELECT * FROM t1; c1 c2 1 1 2 2 INSERT INTO t1 VALUES(NULL, 3); INSERT INTO t1 VALUES(NULL, 4); SELECT * FROM t1; c1 c2 1 1 2 2 3 3 4 4 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' DROP TABLE IF EXISTS t2; Warnings: Note 1051 Unknown table 't2' CREATE TABLE t1 ( a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, b INT(10) UNSIGNED NOT NULL, c ENUM('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (a)) ENGINE = InnoDB; CREATE TABLE t2 ( m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, n INT(10) UNSIGNED NOT NULL, o enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (m)) ENGINE = InnoDB; INSERT INTO t2 (n,o) VALUES (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false'); SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `m` int(11) unsigned NOT NULL AUTO_INCREMENT, `n` int(10) unsigned NOT NULL, `o` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`m`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; SELECT * FROM t1; a b c 1 1 TRUE 2 1 FALSE 3 2 TRUE 4 2 FALSE 5 3 TRUE 6 3 FALSE 7 4 TRUE 8 4 FALSE 9 5 TRUE 10 5 FALSE 13 1 TRUE 14 1 FALSE 15 2 TRUE 16 2 FALSE 17 3 TRUE 18 3 FALSE 19 4 TRUE 20 4 FALSE 21 5 TRUE 22 5 FALSE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SELECT * FROM t1; a b c 1 1 TRUE 2 1 FALSE 3 2 TRUE 4 2 FALSE 5 3 TRUE 6 3 FALSE 7 4 TRUE 8 4 FALSE 9 5 TRUE 10 5 FALSE 13 1 TRUE 14 1 FALSE 15 2 TRUE 16 2 FALSE 17 3 TRUE 18 3 FALSE 19 4 TRUE 20 4 FALSE 21 5 TRUE 22 5 FALSE 23 1 FALSE 24 2 FALSE 25 3 FALSE 26 4 FALSE 27 5 FALSE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SELECT * FROM t1; a b c 1 1 TRUE 2 1 FALSE 3 2 TRUE 4 2 FALSE 5 3 TRUE 6 3 FALSE 7 4 TRUE 8 4 FALSE 9 5 TRUE 10 5 FALSE 13 1 TRUE 14 1 FALSE 15 2 TRUE 16 2 FALSE 17 3 TRUE 18 3 FALSE 19 4 TRUE 20 4 FALSE 21 5 TRUE 22 5 FALSE 23 1 FALSE 24 2 FALSE 25 3 FALSE 26 4 FALSE 27 5 FALSE 30 1 FALSE 31 2 FALSE 32 3 FALSE 33 4 FALSE 34 5 FALSE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; SELECT * FROM t1; a b c 1 1 TRUE 2 1 FALSE 3 2 TRUE 4 2 FALSE 5 3 TRUE 6 3 FALSE 7 4 TRUE 8 4 FALSE 9 5 TRUE 10 5 FALSE 13 1 TRUE 14 1 FALSE 15 2 TRUE 16 2 FALSE 17 3 TRUE 18 3 FALSE 19 4 TRUE 20 4 FALSE 21 5 TRUE 22 5 FALSE 23 1 FALSE 24 2 FALSE 25 3 FALSE 26 4 FALSE 27 5 FALSE 30 1 FALSE 31 2 FALSE 32 3 FALSE 33 4 FALSE 34 5 FALSE 37 1 FALSE 38 2 FALSE 39 3 FALSE 40 4 FALSE 41 5 FALSE 44 1 FALSE 45 2 FALSE 46 3 FALSE 47 4 FALSE 48 5 FALSE 51 1 FALSE 52 2 FALSE 53 3 FALSE 54 4 FALSE 55 5 FALSE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=latin1 DROP TABLE t1; DROP TABLE t2; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' DROP TABLE IF EXISTS t2; Warnings: Note 1051 Unknown table 't2' CREATE TABLE t1( c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); CREATE TABLE t2( c1 TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t2 SELECT c1 FROM t1; Got one of the listed errors INSERT INTO t2 SELECT NULL FROM t1; Got one of the listed errors DROP TABLE t1; DROP TABLE t2; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SHOW VARIABLES LIKE "%auto_inc%"; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-127, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` tinyint(4) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -127 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-127, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-32767, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` smallint(6) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -32767 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-32757, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-8388607, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` mediumint(9) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -8388607 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-8388607, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-2147483647, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -2147483647 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-2147483647, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` bigint(20) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 -9223372036854775807 innodb -1 innodb 1 NULL 2 NULL DROP TABLE t1; CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, NULL); INSERT INTO t1 VALUES (-1, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 SELECT * FROM t1; c1 c2 1 NULL 2 innodb 3 innodb 4 NULL DROP TABLE t1; CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB; CREATE INDEX i1 on t1(c2); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `i1` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 INSERT INTO t1 (c2) values (0); SELECT * FROM t1; c1 c2 10 0 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); INSERT INTO t1(C2) VALUES ('innodb'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `C1` double NOT NULL AUTO_INCREMENT, `C2` char(10) DEFAULT NULL, PRIMARY KEY (`C1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); INSERT INTO t1(C2) VALUES ('innodb'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `C1` float NOT NULL AUTO_INCREMENT, `C2` char(10) DEFAULT NULL, PRIMARY KEY (`C1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 't1' CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 SET c1 = 1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 INSERT INTO t1 SET c1 = 2; INSERT INTO t1 SET c1 = -1; SELECT * FROM t1; c1 -1 1 2 INSERT INTO t1 SET c1 = -1; Got one of the listed errors SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 REPLACE INTO t1 VALUES (-1); SELECT * FROM t1; c1 -1 1 2 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 DROP TABLE t1;