SELECT IFNULL((SELECT ‘Y’ from yimei.boy where grade = 54 limit 1),‘N’)，mysql中用ifnull，hive中可以用isnull，这个用来替代case when count(*) > 0 then ‘Y’ else ‘N’ end ,这种会采用聚合索引查询，浪费内存，但是上面那个不会，同时limit1限制了只要查询到一条就会及时撤销

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);

</insert>