在数据库表设计的时候,经常需要设定字段的默认值,在插入数据时会出现以下的情况:
从性能的角度考虑,一般来说字段会设置为NOT NULL。
这样在strict模式下会直接报错,解决方法是使用DEFAULT关键词。
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的默认值插入进去了。
然而使用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的效率高太多,所以值得这么做。
参考资料: