mysql---split form（拆表并将数据导入同步）

接着上一篇的操作：mysql------inner/left/right join on
1 insert into good_cates(cate_name) select distinct goods.cate_name from goods;
2 insert into good_cates(cate_name) select goods.cate_name from goods group by goods.cate_name;
这里的good_cates是新建的表用于存放商品名称的。
两种方式都实现了数据的导入同步

+----+--------------+
| id | cate_name    |
+----+--------------+
|  8 | computer     |
|  9 | mobile_phone |
| 10 | cpu-device   |
| 11 | gpu-device   |
+----+--------------+

使用外键限制主表中的信息的插入
alter table goods add foreign key(需要添加外键的字段名) reference goods_cate(参考的字段名称)

alter table goods add foreign key(cate_name)  reference goods_cate(id);

mysql> select * from goods;
+----+--------+-------+--------------+
| id | name   | price | cate_name    |
+----+--------+-------+--------------+
|  1 | apple  |  8888 | computer     |
|  2 | huawei |  6888 | computer     |
|  3 | lenvo  |  5888 | computer     |
|  4 | oppo   |  3888 | mobile_phone |
|  5 | xiaomi |  2888 | mobile_phone |
|  6 | vivo   |  2888 | mobile_phone |
|  7 | dell   |  7888 | computer     |
|  8 | HP     |  7888 | computer     |
|  9 | AMD    |  3888 | cpu-device   |
| 10 | intel  |  4888 | cpu-device   |
| 11 | nvidia |  1888 | gpu-device   |
| 12 | AMD    |   888 | gpu-device   |
+----+--------+-------+--------------+

1 update goods inner join good_cate on goods.cate_name=good_cate.name set goods.cate_name=good_cate.id;

+----+--------+-------+-----------+
| id | name   | price | cate_name |
+----+--------+-------+-----------+
|  1 | apple  |  8888 | 1         |
|  2 | huawei |  6888 | 1         |
|  3 | lenvo  |  5888 | 1         |
|  4 | oppo   |  3888 | 4         |
|  5 | xiaomi |  2888 | 4         |
|  6 | vivo   |  2888 | 4         |
|  7 | dell   |  7888 | 1         |
|  8 | HP     |  7888 | 1         |
|  9 | AMD    |  3888 | 2         |
| 10 | intel  |  4888 | 2         |
| 11 | nvidia |  1888 | 3         |
| 12 | AMD    |   888 | 3         |
+----+--------+-------+-----------+
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name      | varchar(30)      | YES  |     | NULL    |                |
| price     | int(10) unsigned | YES  |     | NULL    |                |
| cate_name | varchar(30)      | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

2 alter table goods add foreign key (cate_id) references good_cate(id);

mysql> select * from goods;
+----+--------+-------+---------+
| id | name   | price | cate_id |
+----+--------+-------+---------+
|  1 | apple  |  8888 |       1 |
|  2 | huawei |  6888 |       1 |
|  3 | lenvo  |  5888 |       1 |
|  4 | oppo   |  3888 |       4 |
|  5 | xiaomi |  2888 |       4 |
|  6 | vivo   |  2888 |       4 |
|  7 | dell   |  7888 |       1 |
|  8 | HP     |  7888 |       1 |
|  9 | AMD    |  3888 |       2 |
| 10 | intel  |  4888 |       2 |
| 11 | nvidia |  1888 |       3 |
| 12 | AMD    |   888 |       3 |
+----+--------+-------+---------+
mysql> desc goods;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(30)      | YES  |     | NULL    |                |
| price   | int(10) unsigned | YES  |     | NULL    |                |
| cate_id | int(10) unsigned | NO   | MUL | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

---

注意这里两次查询goods表的不同之处（下面的cate_id是cate_name变过来的）

---