Mybatis检测mysql表是否存在

原文1
原文2

1、优先使用information_schema来检查，如果没有查询这个的权限则使用show tables来检查。

mapper：

package com.chenp.demo.dao;

import java.util.Map;
import org.apache.ibatis.annotations.Param;
 
/**
 * 通用的mapper
 * @author cp218
 * @data 2022/12/9 15:57
 *
 */
public interface CommonMapper {
	
    /**
     * 使用information_schema检查表是否存在
     * @param tableSchema
     * @param tableName
     * @return
     */
    Integer checkTableExistsWithSchema(@Param("tableSchema")String tableSchema, @Param("tableName")String tableName);
 
    /**
     * 使用show tables检查表是否存在
     * @param tableName
     * @return
     */
    Map<String, String> checkTableExistsWithShow(@Param("tableName")String tableName);
}

xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.chenp.demo.mapper.CommonMapper">

    <select id="checkTableExistsWithSchema"
        resultType="java.lang.Integer">
        SELECT 
        	COUNT(1) 
        FROM information_schema.tables 
        WHERE
        	table_schema = #{tableSchema} 
        	AND table_name = #{tableName}
    </select>
 
    <select id="checkTableExistsWithShow"
        resultType="java.util.Map">
        show tables like #{tableName}
    </select>
    
</mapper>

通用service：

package com.chenp.demo.service;
 
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import com.yangzhilong.mapper.CommonMapper;
 
import lombok.extern.slf4j.Slf4j;
 
@Service
@Slf4j
public class CommonService {
	
    private static final String TABLE_SCHEMA = "cp_user";
    @Autowired
    private CommonMapper commonMapper;
 
    /**
     * 检查表是否存在
     * @param tableName
     * @return
     */
    public boolean checkTableExists(String tableName) {
        try {
            Integer count = commonMapper.checkTableExistsWithSchema(TABLE_SCHEMA, tableName);
            return count == 1;
        } catch (Exception e) {
            log.error("使用information_schema检测表失败", e);
            Map<String, String> list = commonMapper.checkTableExistsWithShow(tableName);
            if(!CollectionUtils.isEmpty(list)) {
                return true;
            }
        }
 
        return false;
    }
}