今天在执行开发发过来的工单的时候,source批量导入执行时候发现报了很多警告 提示 truncate for column xxxxx 。导入完成后,使用select查询后,发现大量数据未成功插入。
后来发现是enum字段没有加引号搞的鬼。
结论:
enum的字段,在插入的时候,必须带上引号。否则会出现不可预期的问题。
验证过程如下:
[none] > use test;
[test] > create table t1(
a int primary key auto_increment,
b enum('4','3','2','1') default '3');
[test] > INSERT INTO t1 (b) VALUES (4);
Query OK, 1 row affected
Time: 0.012s
[test] > INSERT INTO t1 (b) VALUES ('4');
Query OK, 1 row affected
Time: 0.012s
[test] > SELECT * from t1;
+-----+-----+
| a | b |
|-----+-----|
| 1 | 1 | ---> 这里我们执行的是 INSERT INTO t1 (b) VALUES (4); 结果却插入的是数值1,和我们实际上的目标结果完全不一致。
| 2 | 4 | ---> 这里我们执行的是 INSERT INTO t1 (b) VALUES ('4'); 这里插入带引号的4,和我们的预期结果一致。
+-----+-----+
原因:
enum类型的字段插入数值的时候, 带引号的时候,插入的才是真正的数值。 如果不带引号插入的话,实际上是插入的key(如上面的例子中 INSERT INTO t1 (b) VALUES (4),插入的是b列第四个default值,也就是取enum('4','3','2','1')第四个默认值,即最终插入的是数值1)。
试验,宽松sql_mode下的插入情况:
[test] > set session sql_mode='';
[test] > INSERT INTO t1 (b) VALUES (5); ---> 插入一个超出enum下标范围的值
Query OK, 1 row affected
Time: 0.012s
[test] > INSERT INTO t1 (b) VALUES ('5'); ---> 插入一个不在enum允许的值
Query OK, 1 row affected
Time: 0.011s
[test] > SELECT * from t1;
+-----+-----+
| a | b |
|-----+-----|
| 1 | 1 |
| 2 | 4 |
| 3 | |
| 4 | |
+-----+-----+
[test] > SELECT * from t1 where b = '';
+-----+-----+
| a | b |
|-----+-----|
| 3 | |
| 4 | |
+-----+-----+
[test] > SELECT * from t1 where b is null;
+-----+-----+
| a | b |
|-----+-----|
+-----+-----+
可以看到在sql_mode为空的时候,虽然插入的时候没有报错,但是实际上查询是没有结果的,(查出来后插入的2行的b是''空值,不是NULL)。
继续试验,严格的sql_mode下异常插入的情况:
[test] > set session sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
[test] > INSERT INTO t1 (b) VALUES ('5');
(1265, u"Data truncated for column 'b' at row 1")
[test] > INSERT INTO t1 (b) VALUES (5);
(1265, u"Data truncated for column 'b' at row 1")
可以看到严格的sql_mode下,我们的异常插入就直接报错了。
ENUM枚举
一般不建议使用,后期不便于扩展。任何不在枚举的范围的值插入都会报错,一般用tinyint替代ENUM比较合适。
ENUM的字段值不区分大小写。如insert into tb1 values("M"); 和insert into tb1 values("m");效果一样的。
补充:
enum的存储原理:
(http://justwinit.cn/post/7354/?utm_source=tuicool&utm_medium=referral)
在建立enum类型的字段时,我们会给他规定一个范围比如 enum('a','b','c'),这时mysql内部会建立一张hash结构的map表,类似:0000 -> a,0001 -> b,0002 -> c。
当我插入一条数据,此字段的值位a或b或c时,他存储在里面的不是这个字符,而是对应他的索引,也就是那个0000或0001或0002。
同样,enum在mysql手册上的说明:
ENUM('value1','value2',...)
1或2个字节,取决于枚举值的个数(最多65,535个值)
除非enum的个数超过了一定数量,否则他所占的存储空间也总是1字节。
转载于:https://blog.51cto.com/lee90/1933603