如何使用 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">SELECTid, uid, login_type, company, company_id, online_time, offline_time, current_state, accumulated_duration, create_time, update_timeFROMuser_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 INTOuser_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=";">UPDATEuser_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">SELECTuid, MAX(create_time) as create_timeFROMuser_online_detail_${tableSuffix}WHEREcompany = #{company}<if test="uid != null">and uid = #{uid}</if>GROUP BY uidORDER BY create_time desc</select><select id="getOnlineUserListByUserId" resultMap="BaseResultMap">SELECTid, uid, login_type, company, company_id, online_time, offline_time, current_state, accumulated_duration, create_time, update_timeFROMuser_online_detail_${tableSuffix}WHEREcreate_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;@PostConstructpublic 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);@Overridepublic 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 ExceptionMybatisSqlSessionFactoryBean 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"));@Overridepublic 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);}@Overrideprotected 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")@Primarypublic 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() {@Overridepublic 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());}};}
}