起始是同学有个需求,怎么实现一张拆成多张表,同时数据也拆好。所有如下探究,简化数据和例子
1、先决条件
-
图示
-
mysql 数据库
-
mysql初始化数据
-
create table A ( no int auto_increment primary key, name varchar(30) null, de varchar(30) null ); INSERT INTO A (no, name, de) VALUES (1, '赵', '部门一'); INSERT INTO A (no, name, de) VALUES (2, '钱', '部门一'); INSERT INTO A (no, name, de) VALUES (3, '孙', '部门一'); INSERT INTO A (no, name, de) VALUES (4, '李', '部门二'); INSERT INTO A (no, name, de) VALUES (5, '周', '部门二'); INSERT INTO A (no, name, de) VALUES (6, '吴', '部门二'); INSERT INTO A (no, name, de) VALUES (7, '郑', '部门三'); INSERT INTO A (no, name, de) VALUES (8, '王', '部门三');
-
注释
- 创建一张表A, 三个字段,no:编号,name:姓名, de:部门名
-
需求按de(部门)拆成多张表,暂且以de的值为表名,sql语句怎么实现?
-
效果图
-
2、思路
- mysql有create select 语法,只是支持生一张表,但是这个是不确定多少个部门的
- 利用select语句自动生成 create select 语句,复制一下就完成分表操作
3、实操
3.1、create select 语句
create table 新表名 select 字段1, 字段2 from 原表名 where 条件;
例子(比如分开部门一所有数据):
create table 部门一 select no, name from A where de='部门一';
3.2、生成一条create table 语句
select CONCAT('create table ', de, ' select no, name from A where de=''', de, ''';') as tab from A limit 1
-
concat : 拼接字符函数
-
效果:
3.3、生成所有create table 语句,去重 (通过group by去重)
select CONCAT('create table ', de, ' select no, name from A where de=''', de, ''';') as tab from A group by de
- 效果
3.4、继续优化,如果100个部门不得复制一百遍吗?能不能将tab的值拼接起来
-
group_concat 字段名 SEPARATOR 分隔符
-
生成sql
-
select group_concat(aa.tab SEPARATOR ' ')from (select CONCAT('create table ', de, ' select no, name from A where de=''', de, ''';') as tab from A group by de ) aa;
-
结果是:
-
create table 部门一 select no, name from A where de='部门一'; create table 部门二 select no, name from A where de='部门二'; create table 部门三 select no, name from A where de='部门三';
-
3.5、复制执行即可。
4、总结
- 本质上是利用mysql的select语句拼接SQL,替换手动拼接sql的麻烦和容易出错
- 然后利用mysql支持语句执行sql功能。例如其他需求,比如订正数据时候,可以利用select生update语句,然后执行,这也一个思路,当然配合excel使用