Mybatis检测mysql表是否存在

我会带着你远行 2024-03-26 04:25 68阅读 0赞

> [原文1][1]  
> [原文2][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;
        }
    }

[1]: https://www.bbsmax.com/A/q4zVLW9K5K/
[2]: https://www.cnblogs.com/yangzhilong/p/10669775.html