Mybatis下的创建与删除索引

事情的起因是这样的，公司安排我做一个提高查询速度的优化，于是我就想到了在数据库添加索引，本来以为此事很简单，写2条语句就能完成，但是却意外的让我踩了很多坑。

MySQL创建与删除索引

在MySQL中，创建普通索引的语句很简单，这里我只列举一种方式，其他方式另查百度即可：

1
2
CREATE INDEX idx_z_9777769dm8x_dh ON z_9777769dm8x(dh);
格式是上面这样，idx_z_9777769dm8x_dh是索引名，一般情况下是由：idx_你的表名_索引列的名称，on 后面是表名，括号中是要加索引的列，可以是多个。

删除普通索引：

1
2
3
DROP INDEX idx_z_475ts5v488v_id ON z_475ts5v488v;
索引名：idx_z_475ts5v488v_id
表名：z_475ts5v488v

引入项目

起初，我以为将此语句改成动态SQL引入项目中，调用接口即可完成功能的优化，但是事情却并不是我想的这么简单。

当我使用上面的语句，并改为动态SQL后：

1
2
3
4
5
6
<sql id="delIndex">
    DROP INDEX #{indexName} ON #{tableName};
</sql>
<update id="delAnIndex">
    <include refid="delIndex" />
</update>
1
2
3
4
5
6
<sql id="createIndex">
  CREATE INDEX #{idx_users_name} ON ${users}(dh);
</sql>
<update id="createUsersIndex">
  <include refid="createIndex" />
</update>

正常调用创建或删除方法并传入值后：

1
Cannot determine value type from string 'z_9777769dm8x'; nested exception is java.sql.SQLDataException: Cannot determine value type from string 'z_9777769dm8x' with root cause

查询后得知：

1
这个异常提示无法从字符串 'z_9777769dm8x' 中确定值类型，这可能是由于 MyBatis 在将字符串转换为参数值时出现了问题。在 MyBatis 中，如果您使用了命名参数或占位符，MyBatis 会尝试将字符串转换为正确的参数类型，以便正确地执行 SQL 查询或更新操作。

然后我就开始了无穷无尽的修改模式，开始把xml里面修改成只有一个占位符的形式，SQL语句在Java中写，传入后仍然是上面这个错误。之后我便查询各方资料，更改了一种xml写法，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!-- 创建索引 -->
<update id="createIndex" parameterType="java.util.Map">
    CREATE INDEX `${params.indexName}` ON `${params.tableName}`(`${params.columnName}`);
</update>

<!-- 删除索引 -->
<update id="dropIndex">
    ALTER TABLE `${params.tableName}` DROP INDEX `${params.indexName}`;
</update>

<!-- 判断索引是否存在 -->
<select id="isIndexExists" resultType="int" parameterType="java.util.Map">
    SELECT COUNT(*) FROM information_schema.statistics
    WHERE table_schema = DATABASE() AND table_name = #{params.tableName} AND index_name = #{params.indexName}
</select>

方法依次是：

1
2
3
int createIndex(@Param("params") Map<String, String> params);
int dropIndex(@Param("params") Map<String, String> params);
int isIndexExists(@Param("params") Map<String, String> params);

此时在调用创建索引的方法，便可以正常执行：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
public String addAllIndex(){
    logger.info("开始创建k_usertable的索引...");
    List<Map<String, Object>> tableNamesAndIds = usertableDao.selectMaps(null);
    List<String> tableNames = tableNamesAndIds.stream()
            .map(map -> (String) map.get("copyTableName"))
            .collect(Collectors.toList());
    List<Long> ids = tableNamesAndIds.stream()
            .map(map -> (Long) map.get("id"))
            .collect(Collectors.toList());
    List<Tcolumn> dhs = new ArrayList<>();
    String dhColumnName;
    for (Map<String, Object> tableNamesAndId : tableNamesAndIds) {
        dhs = tcolumnDao.selectTcolumnByDisplaylabel(tableNamesAndId.get("id").toString(), "档号");
        if (dhs.size() != 0) {
            dhColumnName = (String) dhs.get(0).getFieldname();
            String indexName = "idx_" + tableNamesAndId.get("copytablename") + "_DH";
            Map<String, String> params = new HashMap<>();
            params.put("indexName", indexName);
            params.put("tableName", (String) tableNamesAndId.get("copytablename"));
            params.put("columnName", dhColumnName);
            int indexExists = usertableDao.isIndexExists(params);
            if (indexExists != 0) {
                continue; // skip to the next iteration of the loop
            }
            usertableDao.createIndex(params);
        }
    }
    logger.info("索引创建完毕");
    return "索引创建完毕";
}

总结

究其原因，我认为是XML中创建和删除索引的写法有问题。不管怎样，算是积累了经验，下次遇到就会有印象。