mybatis动态建表，向动态表名，动态属性表中插入数据，更新数据

1.mybatis动态创建表相关SQL命令

注意：动态创建表时，使用${}符号，此时是文本格式，可以正常创建。
而使用#{}符号，此时会预编译，动态创建时获取不到文本值而报错。

SELECT
count(1)
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA =(
SELECT DATABASE
())
AND TABLE_NAME = #{tableName}

CREATE TABLE ${tableName} ( ${item.columnName} ${item.columnType}(${item.columnLength}) NOT NULL COMMENT #{item.columnComment}

<foreach item="item" index="index" collection="columns">
    , INDEX ${item.indexName}(${item.columnName}) USING BTREE
</foreach>
)ENGINE = InnoDB CHARACTER SET = utf8

<!--展示表索引-->
<select id="showTableIndex" parameterType="string" resultType="map">
    show index from ${tableName}
</select>

<!--获取表字段-->
<select id="showTableColumn" parameterType="string" resultType="map">
    SELECT
        COLUMN_NAME,
        COLUMN_TYPE,
        IS_NULLABLE,
        COLUMN_COMMENT
    FROM
        information_schema. COLUMNS
    WHERE TABLE_NAME = ${tableName}
</select>

<!--动态删除索引-->
<update id="deleteTableIndex1" parameterType="com.cochain.baas.api.entity.DataModelColumn">
    DROP INDEX ${indexName} ON ${tableName}
</update>

<!--动态删除索引-->
<update id="deleteTableIndex2" parameterType="com.cochain.baas.api.entity.DataModelColumn">
    ALTER TABLE ${tableName} DROP INDEX ${indexName}
</update>

<!--获取表注释-->
<select id="showTableComment" parameterType="string" resultType="string">
    SELECT
        TABLE_COMMENT
    FROM
        information_schema.TABLES
    WHERE
    TABLE_SCHEMA =(
    SELECT DATABASE
               ())
    AND TABLE_NAME = ${tableName}
</select>

<!--创建普通索引-->
<update id="addNormalTableIndex" parameterType="com.cochain.baas.api.entity.DataModelColumn">
    ALTER TABLE ${tableName} ADD INDEX ${indexName} ( ${columnName} )
</update>

<!--增加表字段-->
<update id="addDataModelColumn" parameterType="com.cochain.baas.api.entity.DataModelColumn">
    <if test="isNullable == 1">
        ALTER TABLE ${tableName} ADD COLUMN ${columnName} ${columnType}(${columnLength}) DEFAULT NULL COMMENT #{columnComment}
    </if>
    <if test="isNullable == 2">
        ALTER TABLE ${tableName} ADD COLUMN ${columnName} ${columnType}(${columnLength}) NOT NULL COMMENT #{columnComment}
    </if>
</update>

<!--删除表字段-->
<update id="deleteDataModelColumn" parameterType="com.cochain.baas.api.entity.DataModelColumn">
    ALTER TABLE ${tableName} DROP COLUMN ${columnName}
</update>

<!--删除表字段-->
<update id="modifyTableName" parameterType="map">
    ALTER TABLE ${oldTableName} RENAME TO ${newTableName}
</update>

2.向动态表中插入数据后，要返回主键id，通过实体类。（若是不需要返回主键，则不需要建实体类）

xml代码：

insert ignore into ${tableName}

KaTeX parse error: Expected 'EOF', got '#' at position 140: …,"> #̲{params.{key}}

实体类代码：
public class TableMap {
// id
private int id;
// 表名
private String tableName;
// 字段名
private Map<String,String> params;

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getTableName() {
    return tableName;
}

public void setTableName(String tableName) {
    this.tableName = tableName;
}

public Map<String, String> getParams() {
    return params;
}

public void setParams(Map<String, String> params) {
    this.params = params;
}

}

2.向动态表名，动态属性表中更新数据

UPDATE ${tableName} set


${key} = #{value}


WHERE


ID = #{value}