在数据库表设计的时候,经常需要设定字段的默认值,在插入数据时会出现以下的情况:
- 如果该字段为
NULL
类型,那么直接将NULL
插入到该字段,字段值就是设定的默认值NULL
; - 如果该字段为
NOT NULL
类型,那么直接将NULL
插入到该字段的话,MySQL普通模式下该字段的值会变成默认值并发出警告,但是strict
模式下会直接报错。
从性能的角度考虑,一般来说字段会设置为NOT NULL
。
这样在strict
模式下会直接报错,解决方法是使用DEFAULT
关键词。
1 官方说明
MySQL官方文档(http://dev.mysql.com/doc/refman/5.6/en/insert.html)中对此有了说明:
说明1
insert
的语法说明(注意加红色的关键字)
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name,…)]
[(col_name,…)]
{VALUES | VALUE} ({expr | DEFAULT},…),(…),…
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] … ]
说明2
使用DEFAULT关键字的说明:
Use the keyword DEFAULT to set a column explicitly to its default value. This makes it easier to write INSERTstatements that assign values to all but a few columns, because it enables you to avoid writing an incompleteVALUES list that does not include a value for each column in the table. Otherwise, you would have to write out the list of column names corresponding to each value in the VALUES list.
翻译过来大致就是说,用关键字DEFAULT
可以显式的在insert
的过程中,将一个字段设定为它的默认值。
比如一条SQL可以是这样的:
insert into student(no, name, age, class) values(1, 'crazyant', '25', DEFAULT)
这时候就直接将班级class
的默认值插入进去了。
2 文件导入的情况
然而使用load data infile to db
,即直接将文件导入到数据表时,却无法使用默认的DEFAULT
值。
因为将该字段设定为DEFAULT
会被认为是个字符串,改成NULL
(用\N
表示)则又会出现将NULL
插入到NOT NULL
的错误。
解决方法是,用程序自动取出数据表的默认值,然后在插入数据之前,自动将数据中的NULL
值替换成数据表该字段的默认值。
如下步骤所示:
步骤1: 使用SHOW COLUMNS
取出数据表字段的默认值
mysql> SHOW COLUMNS FROM City;
+------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | Country | char(3) | NO | UNI | -1 | | | District | char(20) | YES | MUL | | | | Population | int(11) | NO | | 0 | | +------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
如上所示,第5列 Default
下的这一列就是数据表字段的默认值,我们可以取出第一列和第五列,放在一个数组或字典里面:
array[id]=NULL array[Name]="" array[Country]=-1 array[District]="" array[Population]=0
步骤2:插入数据前,替换其中的NULL
为字典存放的默认值
在插入数据表之前,如果某个字段的值为NULL
,就根据字段名从上述字典取出默认值,替换掉NULL
,然后再插入数据。
这种方法在load data from file
中可用.
因为load data
的效率比insert
的效率高太多,所以值得这么做。
参考资料: