MySQL中插入数据突然发生错误,只有两行插入执行成功了,其他未插入数据中。
执行语句:
INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic3',0,NOW());
INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic4',0,NOW());
INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic5',1,NOW());
INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic6',2,NOW());
INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic7',0,NOW());
INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic8',0,NOW());
执行结果信息:
mysql> INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic3',0,NOW());
ERROR 1265 (01000): Data truncated for column 'status' at row 1
mysql> INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic4',0,NOW());
ERROR 1265 (01000): Data truncated for column 'status' at row 1
mysql> INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic5',1,NOW());
tatus`,date_recorded) VALUES('devic6',2,NOW());
INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic7',0,NOW());
INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic8',0,NOW());Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic6',2,NOW());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic7',0,NOW());
ERROR 1265 (01000): Data truncated for column 'status' at row 1
mysql> INSERT INTO device(`code`,`status`,date_recorded) VALUES('devic8',0,NOW());commit;
ERROR 1265 (01000): Data truncated for column 'status' at row 1
Query OK, 0 rows affected (0.00 sec)
以下是表结构:
/*设备表*/
DROP TABLE IF EXISTS device;
CREATE TABLE device( id INT(32) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '设备id,主键', `code` VARCHAR(128) UNIQUE NOT NULL COMMENT '设备唯一码', `status` ENUM('0','1','2') NOT NULL DEFAULT '2' COMMENT '设备状态,2未连接,0关,1开', date_recorded DATETIME NOT NULL COMMENT '录入时间' )ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='设备表';
问题所在:
status类型是enum类型,插入时却是int,之所以能有两句成功应该是mysql的bug。