利用Excel快速生成mysql语句

数据准备

创建数据库

CREATE TABLE `t_label_type_bind` (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `label_id` bigint(20) NOT NULL COMMENT '标签id',
  `label_type` varchar(2)  DEFAULT NULL COMMENT '标签类型;10-课程位置标签；20-轮播图标签 字典值labelType',
  `train_project_id` bigint(20) NOT NULL COMMENT '培训项目id',
  `TENANT_ID` bigint(20) NOT NULL COMMENT '商户id',
  `CREATED_BY` varchar(32)  DEFAULT NULL COMMENT '创建人',
  `CREATED_TIME` datetime DEFAULT NULL COMMENT '创建时间',
  `UPDATED_BY` varchar(32)  DEFAULT NULL COMMENT '更新人',
  `UPDATED_TIME` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) COMMENT='标签位置类型绑定表';

初始化一条数据

INSERT INTO t_label_type_bind (id,label_id,label_type,train_project_id,TENANT_ID,CREATED_BY,CREATED_TIME,UPDATED_BY,UPDATED_TIME) VALUES
	 (8000000000000019722,108,'10',1,2017032717500543538,'2022032819022140927','2023-02-17 09:32:51.0','2022032819022140927','2023-02-17 09:32:51.0');

大批量新增数据

1. 新建excel文件，做好需要导入的数据
   
   2.复制mysql的添加或者修改语句，这里以新增为例

INSERT INTO t_label_type_bind (id,label_id,label_type,train_project_id,TENANT_ID,CREATED_BY,CREATED_TIME,UPDATED_BY,UPDATED_TIME) VALUES
	 (8000000000000019722,108,'10',1,2017032717500543538,'2022032819022140927','2023-02-17 09:32:51.0','2022032819022140927','2023-02-17 09:32:51.0');

3.修改insert语句，改为匹配excel文件内容的样式

• 尾部的分号改为逗号
• 前后添加双引号
• 需要替换的值,改为excel内容样式
  • 数字格式： “&A2&”
  • 文本格式：‘“&A2&”’

"("&A2&","&B2&",10,"&C2&",2017032717500543538,'admin','NOW()','admin','NOW()'),"

4.添加到excel文件
先写等于号 再粘贴内容 然后按下回车

5. 按住右下角，往下拖动，生成批量insert语句，把最后一条逗号改为分号

6.复制生成的数据，整合insert语句,以部分数据为例

INSERT INTO t_label_type_bind (id,label_id,label_type,train_project_id,TENANT_ID,CREATED_BY,CREATED_TIME,UPDATED_BY,UPDATED_TIME) VALUES	 
(8000000000000019722,100,'10',1562337535374528512,2017032717500543538,'admin','NOW()','admin','NOW()'),
(8000000000000019723,101,'10',1562337535374528512,2017032717500543538,'admin','NOW()','admin','NOW()'),
(8000000000000097011,1001,'10',2022101913390000001,2017032717500543538,'admin','NOW()','admin','NOW()'),
(8000000000000097012,1002,'10',2022101913390000001,2017032717500543538,'admin','NOW()','admin','NOW()');