如何使用 SQL 语句创建一个 MySQL 数据库的表，以及对应的 XML 文件和 Mapper 文件

这个方式通常是放在项目代码中，使用配置在项目的启动时创建表格，SQL 语句放到一个 XML 文件中。在Spring 项目启动时，通过配置的方式和 Bean的方式进行加载，并创建文件，同时做了些部分延伸。

1、SQL 脚本语句

DROP TABLE IF EXISTS `user_online_detail_${tableSuffix}`;
CREATE TABLE `user_online_detail_${tableSuffix}`  (
`id` bigint(20) NOT NULL COMMENT 'id',
`uid` bigint(20) DEFAULT NULL COMMENT '用户id',
`login_type` int(11) DEFAULT NULL COMMENT '端类型',
`company` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '公司name',
`company_id` char(36) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '公司id',
`online_time` bigint(20) DEFAULT NULL COMMENT '上线时间',
`offline_time` bigint(20) DEFAULT NULL COMMENT '下线时间',
`current_state` int(11) DEFAULT NULL COMMENT '当前状态',
`accumulated_duration` bigint(20) DEFAULT NULL COMMENT '累计时长',
`create_time` bigint(20) DEFAULT NULL COMMENT '记录创建时间',
`update_time` bigint(20) DEFAULT NULL COMMENT '记录修改时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `uid`(`uid`) USING BTREE,
INDEX `login_type`(`login_type`) USING BTREE,
INDEX `company`(`company`) USING BTREE,
INDEX `create_time`(`create_time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

注意事项：

• user_online_detail_${tableSuffix}：tableSuffix 是表后缀，用于分库分表使用。
• bigint(20)：常用于 Double 类型，用作金额等。
• PRIMARY KEY (id) USING BTREE：创建主键并整加索引，便于搜索，使用 BTREE 索引。
• ENGINE = InnoDB：指定数据库引擎。
• CHARACTER SET = utf8：设定字符集。

2、XML 文件

• 通常开发中，所有的 update 和 insert 都要使用批量的方式。

<?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.wen.mapper.compliance.OnlineUserMapper">
    <resultMap id="BaseResultMap" type="com.wen.dto.UserInfoDto">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="uid" jdbcType="BIGINT" property="uid"/>
        <result column="login_type" jdbcType="INTEGER" property="loginType"/>
        <result column="company" jdbcType="VARCHAR" property="company"/>
        <result column="company_id" jdbcType="CHAR" property="companyId"/>
        <result column="online_time" jdbcType="BIGINT" property="onlineTime"/>
        <result column="offline_time" jdbcType="BIGINT" property="offlineTime"/>
        <result column="current_state" jdbcType="INTEGER" property="currentState"/>
        <result column="accumulated_duration" jdbcType="BIGINT" property="accumulatedDuration"/>
        <result column="create_time" jdbcType="BIGINT" property="createTime"/>
        <result column="update_time" jdbcType="BIGINT" property="updateTime"/>
    </resultMap>

	<sql id="Base_Column_List">
		id, uid, login_type, company, company_id, online_time, offline_time, current_state,
		accumulated_duration, create_time, update_time
	</sql>
	<select id="selectOnlineUserInfoByUid" resultMap="BaseResultMap">
	    SELECT
	    id, uid, login_type, company, company_id, online_time, offline_time, current_state, 
	    accumulated_duration, create_time, update_time
	    FROM
	    user_online_detail_${tableSuffix}
	    WHERE
	    <if test="user != null and user.size() > 0">
	    (uid, login_type, company) in
	    <foreach collection="user" separator="," open="(" close=")" item="u">
	    	(#{u.uid}, #{u.loginType}, #{u.company})
	    </foreach>
	    </if>
	</select>
	
	<insert id="insertOnlineUserInfo" parameterType="com.wen.dto.UserInfoDto">
		INSERT INTO
	    user_online_detail_${tableSuffix}
	    (id, uid, login_type, company, company_id, online_time, offline_time, current_state, 
	    accumulated_duration, create_time, update_time)
	    VALUES
	    <foreach collection="infoList" item="info" separator=",">
	    	(#{info.id,jdbcType=BIGINT}, #{info.uid,jdbcType=BIGINT}, 
	    	#{info.loginType,jdbcType=INTEGER}, #{info.company,jdbcType=VARCHAR}, 
	    	#{info.companyId,jdbcType=CHAR}, #{info.onlineTime,jdbcType=BIGINT}, 
	    	#{info.offlineTime,jdbcType=BIGINT}, #{info.currentState,jdbcType=INTEGER}, 
	    	#{info.accumulatedDuration,jdbcType=BIGINT}, #{info.createTime,jdbcType=BIGINT},
	        #{info.updateTime,jdbcType=BIGINT})
		</foreach>
	</insert>
	
	<update id="updateOnlineUserInfo">
		<foreach collection="infoList" item="info" separator=";">
			UPDATE
			user_online_detail_${tableSuffix}
			<set>
				<if test="info.company != null">
					company = #{info.company,jdbcType=VARCHAR},
	            </if>
	            <if test="info.companyId != null">
	                company_id = #{info.companyId,jdbcType=CHAR},
	            </if>
	            <if test="info.onlineTime != null">
	                online_time = #{info.onlineTime,jdbcType=BIGINT},
	            </if>
	            <if test="info.offlineTime != null">
	                offline_time = #{info.offlineTime,jdbcType=BIGINT},
	            </if>
	            <if test="info.currentState != null">
	                current_state = #{info.currentState,jdbcType=INTEGER},
	            </if>
	            <if test="info.updateTime != null">
	                update_time = #{info.updateTime,jdbcType=BIGINT},
	            </if>
	            <if test="info.accumulatedDuration != null">
	                accumulated_duration = #{info.accumulatedDuration,jdbcType=BIGINT}
	            </if>
			</set>
	        WHERE id = #{info.id}
		</foreach>
	</update>
	
	<select id="getOnlineUserIdByPageHelper" resultType="com.wen.dto.UidDto">
        SELECT
        uid, MAX(create_time) as create_time
        FROM
        user_online_detail_${tableSuffix}
        WHERE
        company = #{company}
        <if test="uid != null">
            and uid = #{uid}
        </if>
        GROUP BY uid
        ORDER BY create_time desc
    </select>

    <select id="getOnlineUserListByUserId" resultMap="BaseResultMap">
        SELECT
        id, uid, login_type, company, company_id, online_time, offline_time, current_state, 
        accumulated_duration, create_time, update_time
        FROM
        user_online_detail_${tableSuffix}
        WHERE
        create_time <![CDATA[>=]]> #{startTime} AND create_time <![CDATA[<=]]> #{endTime}
        <if test="uidList.size() > 0">
            AND uid in
            <foreach collection="uidList" open="(" close=")" item="uid" separator=",">
                #{uid}
            </foreach>
        </if>
        <if test="loginEquip != 0">
            AND login_type = #{loginEquip}
        </if>
        <if test="equipState == 0">
            AND current_state != 5
        </if>
        <if test="equipState == 1">
            AND current_state = 5
        </if>
    </select>

</mapper>

3、Mapper 文件

• @Mapper：这个注解一般是加到配置文件中，用于扫描一个包下的所有 mapper 文件。

@Mapper
public interface OnlineUserMapper {

    List<UserInfoDto> selectOnlineUserInfoByUid(
    		@Param("user") List<QueryUidDto> user, 
    		@Param("tableSuffix") int tableSuffix);

	// 批量方式
    void insertOnlineUserInfo(
    		@Param("infoList") List<UserInfoDto> infoList, 
    		@Param("tableSuffix") int tableSuffix);
	
	// 批量方式
    void updateUserInfo(
    		@Param("infoList") List<UserInfoDto> infoList, 
    		@Param("tableSuffix") int tableSuffix);

    List<UidDto> getOnlineUserIdByPageHelper(
    		@Param("uid") Long uid, 
    		@Param("company") String company, 
    		@Param("tableSuffix") int tableSuffix);
	
	// 批量方式
    List<UserInfoDto> getOnlineUserListByUserId(
            @Param("uidList") List<Long> uidList,
            @Param("startTime") long startTime,
            @Param("endTime") long endTime,
            @Param("loginEquip") int loginEquip,
            @Param("equipState") int equipState,
            @Param("tableSuffix") int tableSuffix);

}

4、启动 ServiceInit 文件

• 该文件作为程序启动时加载。

@Slf4j
@Component
public class ServerInit {

    @Value("${runScript}")
    private boolean runScript;

    @Value("${runScriptDB}")
    private String dbList;

    @PostConstruct
    public void run() {
        log.info("-------------------runScript start!------------------------");
        this.runScript();
        log.info("-------------------runScript end!------------------------");
    }

	// 启动方法
    public void runScript() {
        if (runScript) {
            String[] dbs = dbList.split(",");
            if(ArrayUtil.isEmpty(dbs)) {
                return;
            }
            dataService.runScript(new ArrayList<>(Arrays.asList(dbs)));
        }
    }
}

5、DataService 文件

• 启动时如何创建表格，创建多少个表格，表的名字如何设置。

public interface IDataService {

    void runScript(List<String> dbList);
    
 	@Override
    public void runScript(List<String> dbList) {
        try {
            if (dbList.contains("compliance")) {
                for (int i = 0; i < 10; i++) {
                    complianceSchemaMapper.runScript(String.valueOf(i));
                    log.info("complianceSchemaMapper runScript num {} completed", i);
                }
                complianceSchemaMapper.runSingleScript();
                log.info("complianceSchemaMapper runScript completed");
            }
            log.info("all runScript completed");
        } catch (Exception ex) {
            log.error("runScript error", ex);
		}
	}
}

6、ComplianceDBConfig 配置文件

• 这个文件比较复杂，可作为参考，可做权限控制，根据登陆时的 ID 进行权限判断。

@Slf4j
@Configuration
@MapperScan(basePackages = {"com.wen.server.mapper.compliance"}, sqlSessionTemplateRef = "complianceSessionTemplate")
public class ComplianceDBConfig {

    @Value("${spring.profiles.active}")
    private String active;

    @Value("${spring.datasource.compliance.url}")
    private String url;

    @Value("${spring.datasource.compliance.username}")
    private String username;

    @Value("${spring.datasource.compliance.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.compliance.passwdServer}")
    private String passwdServer;

    @Value("${spring.datasource.compliance.password}")
    private String password;

    @Value("${spring.datasource.compliance.maxWait}")
    private long maxWait;

    @Value("${spring.datasource.compliance.phyTimeoutMillis}")
    private long phyTimeoutMillis;

    @Value("${spring.datasource.compliance.minEvictableIdleTimeMillis}")
    private long minEvictableIdleTimeMillis;

    @Value("${spring.datasource.compliance.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.compliance.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.compliance.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.compliance.timeBetweenEvictionRunsMillis}")
    private long timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.compliance.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.compliance.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.compliance.testOnReturn}")
    private boolean testOnReturn;

    @Value("${spring.datasource.compliance.validationQueryTimeout}")
    private int validationQueryTimeout;

    @Value("${spring.datasource.compliance.connectTimeout}")
    private int connectTimeout;

    @Value("${spring.datasource.compliance.socketTimeout}")
    private int socketTimeout;

    @Value("${spring.datasource.compliance.fetchSize}")
    private int fetchSize;

    private static final String IP_REGEX = "((2[0-4]\\d|25[0-5]|[01]?\\d\\d?)\\.){3}(2[0-4]\\d|25[0-5]|[01]?\\d\\d?)";
 
	@Bean(name = "complianceDataSource")
    public DataSource mysqlDataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(driverClassName);
        druidDataSource.setUsername(username);
        druidDataSource.setUrl(url);
        druidDataSource.setMaxWait(maxWait);
        druidDataSource.setPhyTimeoutMillis(phyTimeoutMillis);
        druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        druidDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setTestWhileIdle(testWhileIdle);
        druidDataSource.setTestOnBorrow(testOnBorrow);
        druidDataSource.setTestOnReturn(testOnReturn);
        druidDataSource.setValidationQueryTimeout(validationQueryTimeout);
        druidDataSource.setConnectTimeout(connectTimeout);
        druidDataSource.setSocketTimeout(socketTimeout);
        if (!active.equals("dev")) {
            druidDataSource.setPasswordCallback(druidPasswordCallback());
        } else {
            druidDataSource.setPassword(password);
        }
        return druidDataSource;
    }

    @Bean(name = "complianceSessionFactory")
    public SqlSessionFactory mysqlSessionFactory(@Qualifier("complianceDataSource") DataSource dataSource) throws Exception {
        //如果用的是mybatis-plus 一定得是 MybatisSqlSessionFactoryBean
        //否则无法识别 mybatis-plus扩展的一些接口,如：selectList, selectOne, selectMap ..., 并出现invalid bound Exception
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //分页插件
        PageInterceptor pageHelper = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        pageHelper.setProperties(properties);
        //添加插件
        bean.setPlugins(pageHelper);
        bean.addMapperLocations(new PathMatchingResourcePatternResolver().getResources("file:./config/*Mapper.xml"));
        bean.addMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/compliance/*.xml"));
        MybatisConfiguration conf = new MybatisConfiguration();
//        conf.setLogImpl(Slf4jImpl.class);
        conf.setDefaultFetchSize(fetchSize);

        addInterceptor(conf);

        bean.setConfiguration(conf);
        return bean.getObject();
    }


    private static void addInterceptor(MybatisConfiguration conf) {

        DeptDataPermissionRule rule = new DeptDataPermissionRule() {

            private final Set<String> userTables = new HashSet<>(Arrays.asList(
                    "user_0", "user_1", "user_2", "user_3", "user_4", "user_5", 
                    "user_6", "user_7", "user_8", "user_9"
            ));
            private final Set<String> deptTables = new HashSet<>(Arrays.asList(
                    "dept_0", "dept_1", "dept_2", "dept_3", "dept_4", "dept_5", 
                    "dept_6", "dept_7", "dept_8", "dept_9"
            ));

            private final Set<String> onlineUserTables = new HashSet<>(Arrays.asList(
                    "user_online_detail_0", "user_online_detail_1", "user_online_detail_2", 
                    "user_online_detail_3", "user_online_detail_4", "user_online_detail_5", 
                    "user_online_detail_6", "user_online_detail_7", "user_online_detail_8", 
                    "user_online_detail_9"
            ));

            @Override
            public boolean matches(String tableName) {
                return userTables.contains(tableName) || deptTables.contains(tableName) || isOnlineUserTables(tableName);
            }

            private boolean isUserTable(String tableName) {
                return userTables.contains(tableName);
            }

            private boolean isDeptTable(String tableName) {
                return deptTables.contains(tableName);
            }

            private boolean isOnlineUserTables(String tableName) { 
            	return userTables.contains(tableName);
            }

    		@Override
            protected Expression getExpression(String tableName, 
            	Alias tableAlias, 
            	CurrentUser loginUser, 
            	DeptDataPermission deptDataPermission) {
                Integer currentPage = UserHolder.getCurrentUser().getCurrentPage();
                if (isUserTable(tableName))
                    return buildUserExpression(tableName, tableAlias, deptDataPermission.getDeptIds(currentPage));
                else if (isDeptTable(tableName))
                    return buildDeptExpression(tableName, tableAlias, deptDataPermission.getDeptIds(currentPage));
                else if (isUserTables(tableName))
                    return buildOnlineUserExpression(tableName, tableAlias, deptDataPermission.getUserIds(currentPage));
                return EXPRESSION_NULL;
            }

            private Expression buildOnlineUserExpression(String tableName, Alias tableAlias, Set<Long> userIds) {
                String columnName = getColumnName(tableName);
                if (StrUtil.isEmpty(columnName)) {
                    return null;
                }
                if (CollUtil.isEmpty(userIds)) {
                    return EXPRESSION_NULL;
                }
                return new InExpression(MyBatisUtils.buildColumn(tableName, tableAlias, columnName),
                        // Parenthesis 的目的，是提供 (1,2,3) 的 () 左右括号
                        new Parenthesis(new ExpressionList<>(convertList(userIds, LongValue::new))));
            }

            private Expression buildDeptExpression(String tableName, Alias tableAlias, Set<Long> deptIds) {
                // 如果不存在配置，则无需作为条件
                String columnName = getColumnName(tableName);

                if (StrUtil.isEmpty(columnName)) {
                    return null;
                }
                // 如果为空，则无条件
                if (CollUtil.isEmpty(deptIds)) {
                    return EXPRESSION_NULL;
                }
                // 拼接条件
                return new InExpression(MyBatisUtils.buildColumn(tableName, tableAlias, columnName),
                        // Parenthesis 的目的，是提供 (1,2,3) 的 () 左右括号
                        new Parenthesis(new ExpressionList<>(convertList(deptIds, LongValue::new))));
            }

            private Expression buildUserExpression(String tableName, Alias tableAlias, Set<Long> deptIds) {
                String columnName = getColumnName(tableName);
                if (StrUtil.isEmpty(columnName)) {
                    return null;
                }
                if (CollUtil.isEmpty(deptIds)) {
                    return new EqualsTo(MyBatisUtils.buildColumn(tableName, tableAlias, "uid"), new LongValue(UserHolder.getCurrentUser().getUserId()));
                }

                return new InExpression(MyBatisUtils.buildColumn(tableName, tableAlias, columnName),
                        // Parenthesis 的目的，是提供 (1,2,3) 的 () 左右括号
                        new Parenthesis(new ExpressionList<>(convertList(deptIds, LongValue::new))));
            }

            private String getColumnName(String tableName) {
                if (StrUtil.isBlank(tableName)) {
                    return null;
                }
                if (isUserTable(tableName)) {
                    return "dept_id";
                }
                if (isDeptTable(tableName)) {
                    return "dept_id";
                }
                if (isOnlineUserTables(tableName) ) {
                    return "uid";
                }
                return null;
            }
        };

        DataPermissionRuleHandler handler = new DataPermissionRuleHandler(new DataPermissionRuleFactoryImpl(Arrays.asList(rule)));
        DataPermissionInterceptor inner = new DataPermissionInterceptor(handler);
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(inner);
        conf.addInterceptor(mybatisPlusInterceptor);
    }

    @Bean("complianceTransactionManager")
    @Primary
    public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("complianceDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean("complianceSessionTemplate")
    public SqlSessionTemplate mysqlSessionTemplate(@Qualifier("complianceSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    public DruidPasswordCallback druidPasswordCallback() {
        return new DruidPasswordCallback() {
            @Override
            public void setProperties(Properties properties) {
                String pwd = PasswordServiceHelper.getLastedPassword(passwdServer, username);
                if (StrUtil.isBlank(pwd)) {
                    log.error("**COMPLIANCE_DB** 密码服务未拿到密码");
                } else {
                    log.info("**COMPLIANCE_DB** get password from password service ok! password: {}", pwd);
                }
                setPassword(pwd.toCharArray());
            }
        };
    }
}