Mysql使用DDL
结论
mybatis开启事务,同时操作dml,ddl,先执行dml后,在执行ddl,最后执行其他逻辑报错,dml,ddl没回滚(测试发现,一旦执行成功ddl,前面的dml就会提交事务)
mysql事务-事务中有ddl语句会自动提交事务
DDL
基本了解
平时我们查询时使用等这些标签,使用DDL使用update即可
动态创建表
需要注意的是平时我们传入参数时使用#{param},如果是数据库名字的话必须使用${tablename}
情景一:字段动态
<sql id="tableColumnFirst">
CREATE TABLE ${tableName}
(
`base_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`account` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`create_time` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`update_time` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除:1已逻辑删除 / 0未逻辑删除',
</sql>
<sql id="tableColumnLast">
PRIMARY KEY (`base_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
</sql>
<update id="addNewTableAndColumn">
<include refid="tableColumnFirst"></include>
<foreach collection="columnMsgs" item="colmnMsg" separator="," close=",">
${colmnMsg}
</foreach>
<include refid="tableColumnLast"></include>
</update>
好处:表与字段一起创建,表与字段同时成功同时失败。避免分别创建时,表创建成功后,列创建失败,但表和之前创建成功的列不会回滚。
情景二:字段静态
完全复制一个基本表(只结构)
<update id="createTable" parameterType="String" >
create table if not exists log_#{time} like log
</update>
部分复制一个基本表(结构和数据)
<update id="createNewTableAndInsertData">
create table ${newTableName} as select id,`name` from ${originalTableName}
</update>
判断表是否存在
<select id="existFormTable" resultType="java.lang.Boolean">
select count(*)
from information_schema.TABLES
where TABLE_NAME = #{tableName};
</select>
查询一个表所有字段
<select id="showColumn" resultType="TableColumn">
show
columns from
${tableName};
</select>
DML
查询
返回参数map形式(key字段,value字段值)
<select id="getExtendValueByAccount" resultType="java.util.Map">
select *
from ${tableName} ext
where ext.account = #{account}
and ext.status = 0
</select>
新增
入参map形式(key字段,value字段值)
<insert id="insertFormExtend">
insert into ${tableName}
<foreach collection="mapParams.keys" item="key" open="(" close=")" separator=",">
${key}
</foreach>
values
<foreach collection="mapParams.keys" item="key" open="(" close=")" separator=",">
#{mapParams[${key}]}
</foreach>
</insert>
修改
入参map形式(key字段,value字段值)
<update id="upFormExtend">
update ${tableName}
set
<foreach collection="mapParams.keys" item="key" separator=",">
${key} = #{mapParams[${key}]}
</foreach>
where base_id = #{baseId}
</update>