MYSQL向数据表插入默认字段值的方法

数据表设计过程中很多时候会设定字段的默认值，那么向该表插入数据时会出现以下情况：

情况1：如果该字段为NULL类型，那么直接将NULL插入到该字段，字段值就自动变成了设定的默认值；

情况2：如果该字段为NOT NULL类型，那么直接将NULL插入到该字段的话，在普通模式下该字段的值会变成默认值并发出警告，但是strict模式下会直接报错；

对于情况2的解决方法是：使用DEFAULT关键词。

官方文档（地址）中对此有了说明：

说明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(sno,sname,sage,class) values(1, 'crazyant', '25', DEFAULT)，这时候就直接将班级class的默认值插入进去了。

然而使用load data infile to db，即直接将文件导入到数据表时，却无法使用默认的DEFAULT值，因为将该字段设定为"DEFAULT"会被认为是个字符串，改成NULL（用\N）表示则又会出现将NULL插入到NOT NULL的错误。

 还有一种方法是程序自动取出数据表的默认值，然后在插入数据之前，自动将数据中的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的效率高太多，所以值得这么做。