mysql添加数据之前判断是否存在

原创已于 2022-06-06 17:03:06 修改 · 7k 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

于 2022-01-12 10:39:10 首次发布

mysql 专栏收录该内容

19 篇文章

订阅专栏

本文介绍MySQL中利用NOT EXISTS子句实现有条件插入的方法，并展示了如何通过预置数据初始化数据库表。具体包括基本配置、角色预置、用户及权限设置等。

mysql支持直接写sql语句做判断，通过查询表数据是否存在，不存在则执行插入操作

示例：

 INSERT INTO tb_news_topic(title,summary) SELECT '218', 
'XQ33019920170811142528217' FROM DUAL WHERE NOT EXISTS(SELECT * 
FROM tb_news_topic WHERE title = '111')

语法：

INSERT INTO table(field1, field2, fieldn) SELECT 'field1', 
'field2', 'fieldn' FROM DUAL WHERE NOT EXISTS(SELECT field FROM 
table WHERE field = ?)

批量操作:
通过foreach遍历数组的时候，foreach里面不能使用await ，所在这个地方我用的for循环对数组进行遍历

for (let i = 0; i < list.data.length;i++){
    let sql = `INSERT INTO tb_news_topic(title,summary,url,topic_order,publishDate,createdAt) SELECT ?, 
?,?,?,?,? FROM DUAL WHERE NOT EXISTS(SELECT * 
FROM tb_news_topic WHERE title = ?)`
      // 直接执行sql语句
      const results = await this.app.mysql.query(sql, [list.data[i].title, 
        list.data[i].summary, 
        '',
        list.data[i].order, 
        new Date(list.data[i].publishDate), 
        this.app.mysql.literals.now,//当前时间
        list.data[i].title
      ]);
 }

预置数据

– 基础数据baseConfig
INSERT IGNORE INTO hlms_toc_basic_config(id,tenant_id,code,name,group,type,value,description)(select (#{tenantId} * 1000000) + id,#{tenantId},code,name,bc.group,type,value,description from hlms_toc_basic_config bc where bc.tenant_id=0);

– 角色预置超级管理员
INSERT IGNORE INTO hlms_toc_role(id, role_name, tenant_id, group_id, role_code, b_default, b_limit, disabled, description, create_by, create_time, last_modified_by, last_modified_time) (SELECT (#{tenantId} * 1000000) + id, role_name, #{tenantId}, group_id, role_code, b_default, b_limit, disabled, description, create_by, create_time, last_modified_by, now() from hlms_toc_role where tenant_id=0);

– 预置admin 用户
insert IGNORE into hlms_toc_user(id,tenant_id,login_name,real_name,password,contact,create_by,create_time,last_modified_by,last_modified_time,pwd_strength,status,is_delete,pwd_modify_time )
(SELECT (#{tenantId}*1000000)+id,#{tenantId},login_name,real_name,password,contact,create_by,create_time,last_modified_by,last_modified_time,pwd_strength,status,is_delete,DATE_SUB(now(),INTERVAL -30 DAY) from hlms_toc_user where tenant_id=0);

– 预置用户角色
insert IGNORE into hlms_toc_user_role(user_id,role_id,tenant_id,disabled,create_by,create_time)(select (#{tenantId}*1000000)+user_id,(#{tenantId}*1000000)+role_id,#{tenantId},disabled,create_by,create_time from hlms_toc_user_role where tenant_id=0 );

– 预置应用
INSERT IGNORE INTO hlms_toc_permission_application(id,name,tenant_id,group_id,code,source,status,suite_id, sys_url,serial_no,description,create_by,create_time,last_modified_by,last_modified_time) (select (#{tenantId}*100000)+id,name,#{tenantId},(#{tenantId}*100000)+group_id,code,source,status,suite_id, sys_url,serial_no,description,create_by,create_time,last_modified_by,now() from hlms_toc_permission_application where tenant_id=0);

– 预置应用扩展表
INSERT IGNORE INTO hlms_toc_permission_application_form(id,application_id,form,icon_url,portal_url,unique_code)
(select (#{tenantId}*1000000)+af.id,(#{tenantId}*1000000)+af.application_id,af.form,af.icon_url,af.portal_url,af.unique_code from hlms_toc_permission_application_form af left join
hlms_toc_permission_application pa on pa.id=af.application_id
where pa.tenant_id=0);

– 功能
INSERT IGNORE INTO hlms_toc_permission_function (id,tenant_id,form_id,parent_id,code,name,status,url,description,unique_code)(select (#{tenantId}*1000000)+id,#{tenantId},(#{tenantId}*1000000)+form_id,parent_id,code,name,status,url,description,unique_code from hlms_toc_permission_function where tenant_id=0);

– 功能按钮
INSERT IGNORE INTO hlms_toc_permission_button (id,tenant_id,function_id,code,name,status,unique_code,is_show,ask_url)(select (#{tenantId}*1000000)+id,#{tenantId},(#{tenantId}*1000000)+function_id,code,name,status,unique_code,is_show,ask_url from hlms_toc_permission_button where tenant_id=0);

– 预置角色配置功能权限
INSERT IGNORE INTO hlms_toc_role_permission(tenant_id,role_id,permission_id,code,type,url)
(select (#{tenantId}*1000000)+tenant_id,(#{tenantId}*1000000)+role_id,(#{tenantId}*1000000)+permission_id,code,type,url from hlms_toc_role_permission where tenant_id=0);