MySQL：向已有数据表中插入新的一列数据

一、需求

有两个表格，test和test2如下所示：

mysql> select * from test;
+------+------+------------+
| data | id   | timestamp  |
+------+------+------------+
|  300 |    3 | 1536292800 |
|  200 |    2 | 1536292808 |
+------+------+------------+

mysql> select * from test2;
+------+------+
| data | id   |
+------+------+
|   10 |    3 |
|   20 |    2 |
+------+------+

1. 需求A

在test表格上直接操作，增加一列为context_time，数据类型为datetime，并赋值为对应的行的UNIX time的datetime，如下表所示（可以延伸为对每一行的某个字段进行相同的运算或函数操作后赋值到新的字段）：

+------+------+------------+---------------------+
| data | id   | timestamp  | context_time        |
+------+------+------------+---------------------+
|  300 |    3 | 1536292800 | 2018-09-07 12:00:00 |
|  200 |    2 | 1536292808 | 2018-09-07 12:00:08 |
+------+------+------------+---------------------+

2. 需求B

将表格test2中的data字段查询出来后根据id拼接到test表格后面，字段名为new_data，如下所示：

+------+------+------------+---------------------+----------+
| data | id   | timestamp  | context_time        | new_data |
+------+------+------------+---------------------+----------+
|  300 |    3 | 1536292800 | 2018-09-07 12:00:00 |       10 |
|  200 |    2 | 1536292808 | 2018-09-07 12:00:08 |       20 |
+------+------+------------+---------------------+----------+

二、解决方案

1. 需求A的解决方案

• 先使用alter命令在原表格test上创建新的字段context_time，数据类型为datetime，默认不为NULL

mysql> alter table test add context_time datetime not NULL;

mysql> select * from test;
+------+------+------------+---------------------+
| data | id   | timestamp  | context_time        |
+------+------+------------+---------------------+
|  300 |    3 | 1536292800 | 0000-00-00 00:00:00 |
|  200 |    2 | 1536292808 | 0000-00-00 00:00:00 |
+------+------+------------+---------------------+

• 然后使用update命令更新context_time的值：

mysql> update test set context_time=from_unixtime(timestamp);

mysql> select * from test;
+------+------+------------+---------------------+
| data | id   | timestamp  | context_time        |
+------+------+------------+---------------------+
|  300 |    3 | 1536292800 | 2018-09-07 12:00:00 |
|  200 |    2 | 1536292808 | 2018-09-07 12:00:08 |
+------+------+------------+---------------------+

2. 需求B的解决方案

• 先使用alter命令在原表格test上创建新的字段new_data，数据类型为int，默认为NULL

mysql> alter table test add new_data int;

mysql> select * from test;
+------+------+------------+---------------------+----------+
| data | id   | timestamp  | context_time        | new_data |
+------+------+------------+---------------------+----------+
|  300 |    3 | 1536292800 | 2018-09-07 12:00:00 |     NULL |
|  200 |    2 | 1536292808 | 2018-09-07 12:00:08 |     NULL |
+------+------+------------+---------------------+----------+

• 然后使用update、inner join、select共同实现更新值：

mysql> update test inner join (select data, id from test2)tmp on test.id=tmp.id set test.new_data=tmp.data;

mysql> select * from test;
+------+------+------------+---------------------+----------+
| data | id   | timestamp  | context_time        | new_data |
+------+------+------------+---------------------+----------+
|  300 |    3 | 1536292800 | 2018-09-07 12:00:00 |       10 |
|  200 |    2 | 1536292808 | 2018-09-07 12:00:08 |       20 |
+------+------+------------+---------------------+----------+

三、结论

• alter命令用于修改表结构，update命令用于修改或更新表的数据（值）
• 不管怎样，要增加一列新的数据，必须先使用alter命令修改原表的结构，增加新的字段、数据类型和默认值
• alter命令还可以修改字段名、调整字段顺序等
• 如果是需求A，仅仅对原表已有字段进行相同操作后作为新字段，那么使用最简单的update、set命令即可
• 如果是需求B，使用inner join就是类似表联结的问题了，必须有主键（id）对应每一行数据，否则更新后的数据整一列都将是查询的第一个数据的值，像这样：

mysql> update test inner join (select data from test2)tmp set test.new_data=tmp.data;

mysql> select * from test;
+------+------+------------+---------------------+----------+
| data | id   | timestamp  | context_time        | new_data |
+------+------+------------+---------------------+----------+
|  300 |    3 | 1536292800 | 2018-09-07 12:00:00 |       10 |
|  200 |    2 | 1536292808 | 2018-09-07 12:00:08 |       10 |
+------+------+------------+---------------------+----------+

mysql> select data from test2;
+------+
| data |
+------+
|   10 |
|   20 |
+------+

• 另外，对于需求B，搜索解决办法的时候遇到有如下的写法，但是mysql中是不可以这样用的，其他SQL server我就没尝试了（我是以学习mysql为主=。=）

mysql> update test set test.new_data=(select data from test2);
ERROR 1242 (21000): Subquery returns more than 1 row

• 最后，一开始我是在折腾insert指令的，但是其实insert是插入行数据，和我想要的并不一样，举个栗子（会增加行然后缺省填充为默认值，这里是NULL）：

mysql> select * from test;
+------+------+
| data | id   |
+------+------+
|  100 |    1 |
|  200 |    2 |
+------+------+

mysql> alter table test add new_data int;

mysql> select * from test;
+------+------+----------+
| data | id   | new_data |
+------+------+----------+
|  100 |    1 |     NULL |
|  200 |    2 |     NULL |
+------+------+----------+

mysql> insert into test(new_data) select (data+1) as new_data from test;

mysql> select * from test;
+------+------+----------+
| data | id   | new_data |
+------+------+----------+
|  100 |    1 |     NULL |
|  200 |    2 |     NULL |
| NULL | NULL |      101 |
| NULL | NULL |      201 |
+------+------+----------+