MySQL初始化/更新脚本及IF NOT EXISTS插入问题求助

一、数据库初始化/迁移脚本的IF NOT EXISTS实现（无需存储过程） #

1. 创建架构对象（表、索引、用户等） #

MySQL原生支持在大部分CREATE语句中直接添加IF NOT EXISTS，可直接写入通用脚本：

• 创建表：CREATE TABLE IF NOT EXISTS permissions_groups (...)
• 创建索引：CREATE INDEX IF NOT EXISTS idx_group_name ON permissions_groups(group_name)
• 创建用户：CREATE USER IF NOT EXISTS 'user'@'localhost' IDENTIFIED BY 'password'

对于MySQL 8.0+版本，修改架构（如添加列）也支持IF NOT EXISTS：

ALTER TABLE permissions_groups ADD COLUMN IF NOT EXISTS new_column VARCHAR(50);

如果是低版本MySQL（低于8.0），可通过查询INFORMATION_SCHEMA判断对象是否存在，再执行动态SQL：

-- 判断列是否存在，不存在则添加
SET @column_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                      WHERE TABLE_SCHEMA = DATABASE() 
                      AND TABLE_NAME = 'permissions_groups' 
                      AND COLUMN_NAME = 'new_column');
SET @sql = IF(@column_exists = 0, 
              'ALTER TABLE permissions_groups ADD COLUMN new_column INT', 
              'SELECT "Column already exists"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2. 插入数据的IF NOT EXISTS逻辑 #

除了你已经验证可行的INSERT ... SELECT ... WHERE NOT EXISTS模式，还可以结合事务保证原子性：

START TRANSACTION;
INSERT INTO permission_groups (group_id, company_id, group_name, notes, date_created, active, deleted)
SELECT 0, 0, 'Billing Admin', 'this groups represents those who do the accounting portion of administrator', NOW(), 1, 0
WHERE NOT EXISTS (SELECT 1 FROM permissions_groups WHERE group_name='Billing Admin');
COMMIT;

二、INSERT IGNORE失效原因及更优解决办法 #

失效原因 #

INSERT IGNORE仅会忽略主键或唯一键冲突的错误。你的表主键是自增group_id，每次插入都会生成新的主键值，不会触发主键冲突；而你用来判断重复的group_name字段没有设置唯一约束，因此MySQL无法识别这是重复行，导致INSERT IGNORE会重复插入数据。

更优解决办法 #

1. 给唯一标识字段添加唯一约束

如果业务上group_name是全局唯一的，先给该字段添加唯一索引：

ALTER TABLE permissions_groups ADD UNIQUE INDEX idx_unique_group_name (group_name);

之后使用INSERT IGNORE就会因为唯一键冲突而跳过重复插入：

INSERT IGNORE INTO permission_groups (company_id, group_name, notes, active, deleted)
VALUES (0, 'Billing Admin', 'this groups represents those who do the accounting portion of administrator', 1, 0);

如果需要在重复时更新其他字段（而非仅跳过），推荐使用INSERT ... ON DUPLICATE KEY UPDATE，原子性更强：

INSERT INTO permission_groups (company_id, group_name, notes, active, deleted)
VALUES (0, 'Billing Admin', 'this groups represents those who do the accounting portion of administrator', 1, 0)
ON DUPLICATE KEY UPDATE
    company_id = VALUES(company_id),
    notes = VALUES(notes),
    active = VALUES(active),
    deleted = VALUES(deleted);

2. 复合唯一约束（多字段组合唯一）

如果允许同一group_name在不同company_id下存在，可创建复合唯一索引：

ALTER TABLE permissions_groups ADD UNIQUE INDEX idx_unique_company_group (company_id, group_name);

再配合INSERT ... ON DUPLICATE KEY UPDATE，就能基于company_id + group_name的组合判断重复，避免误判。

3. 高并发场景的注意事项

INSERT ... SELECT ... WHERE NOT EXISTS在高并发下可能出现竞态条件（多个请求同时判断不存在，导致重复插入），而添加唯一约束配合ON DUPLICATE KEY UPDATE是数据库层面的原子操作，能彻底避免这个问题。

内容的提问来源于stack exchange，提问作者JustMe