Excel生成sql语句

谁借莪１个温暖的怀抱￠ 2021-12-03 15:13 444阅读 0赞

# 1、工具 #

Navicat

# 2、表结构和Excel #

CREATE TABLE `user` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
      `name` varchar(255) NOT NULL COMMENT '姓名',
      `age` int(11) NOT NULL COMMENT '年龄',
      `status` int(11) NOT NULL DEFAULT '0' COMMENT '状态',
      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE KEY `name` (`name`,`age`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=46823 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Excel中的字段与表字段一致

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70][]

# 3、导入 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 3][]

根据自己的情况判断是否需要新建表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 5][]

根据自己情况判断那种情况

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 7][]

插入完成

# 4、生成sql #

比如需要得到id=298的插入语句

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 8][]

生成语句：

INSERT INTO `test`.`user`(`id`, `name`, `age`, `status`, `create_time`, `update_time`) VALUES (298, '李四12', 24, 0, '2019-06-25 19:49:55', '2019-06-25 20:42:40');

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70]: /images/20211203/b1b69401a6ea4e2a95f376c3df3acc88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 1]: /images/20211203/92cdfd37c5fd41c982c4ce2bee872aa5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 2]: /images/20211203/33b66e19ea6b46ac9215998584d8331e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 3]: /images/20211203/dd8e178ce53a4205b5b6d36185f066a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 4]: /images/20211203/a3e5ba6758c54a1db1c876ed70b5d194.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 5]: /images/20211203/f11dcea347854b6db266c6fabd3af7d1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 6]: /images/20211203/ad14745e07bb41da83b0164057f6d21c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 7]: /images/20211203/ce50584592804b41be08832547c8d43d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpeGluZ3poZTI_size_16_color_FFFFFF_t_70 8]: /images/20211203/e85c86aac15f46dd95941542d069f8d2.png