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 w_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 w_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 w_data=(select data from test2);
ERROR1242 (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 |insert语句字段顺序
+------+------+----------+
| 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 |
+------+------+----------+
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论