时间戳

首页 > 解决方案 > 如果表为空，则插入多条记录

mysql - 如果表为空，则插入多条记录

问题描述

我有一个场景，如果表为空，我必须插入多条记录。每次我运行我的服务器时，我的 sql 脚本都会运行。我不使用 H2 数据库，所以我可以插入重复键。我使用 myBatis。我希望仅在表为空时插入查询。

我的插入查询如下：

LOCK TABLES `t002_seat_mst` WRITE;
INSERT INTO `t002_seat_mst` VALUES (2020,1,1,0,0,1,0,'228A'),(2021,1,2,0,0,1,0,'229A'),
    (2022,1,3,0,0,1,0,'231A'),(2023,1,4,0,0,1,0,'233A'),(2024,1,5,0,0,1,0,'235A'),
    (2025,1,6,0,0,1,0,'237A'),(2026,1,7,0,0,1,0,'239A'),(2027,1,8,0,0,1,0,'240A'),
    (2028,1,9,0,0,1,0,'242A'),(2029,1,10,0,0,1,0,'244A'),(2030,1,11,0,0,1,0,'246A');
UNLOCK TABLES;

我怎样才能做到这一点？

标签： mysqlsqlspring-booth2mybatis

解决方案

测试

LOCK TABLES `t002_seat_mst` AS t1 WRITE, `t002_seat_mst` AS t2 READ;

INSERT INTO t002_seat_mst AS t1
SELECT * 
FROM ( SELECT 2020 a,1 b,1 c,0 d,0 e,1 f,0 g,'228A' h UNION ALL
       SELECT 2021,1,2,0,0,1,0,'229A' UNION ALL
       SELECT 2022,1,3,0,0,1,0,'231A' UNION ALL
       SELECT 2023,1,4,0,0,1,0,'233A' UNION ALL
       SELECT 2024,1,5,0,0,1,0,'235A' UNION ALL
       SELECT 2025,1,6,0,0,1,0,'237A' UNION ALL
       SELECT 2026,1,7,0,0,1,0,'239A' UNION ALL
       SELECT 2027,1,8,0,0,1,0,'240A' UNION ALL
       SELECT 2028,1,9,0,0,1,0,'242A' UNION ALL
       SELECT 2029,1,10,0,0,1,0,'244A' UNION ALL
       SELECT 2030,1,11,0,0,1,0,'246A' ) data
WHERE NOT EXISTS ( SELECT NULL
                   FROM t002_seat_mst AS t2 );

UNLOCK TABLES;

推荐阅读