本文已参与「新人创作礼」活动，一起开启掘金创作之路。
mysql8 零时表在程序中的应用
1. 零时表概念
零时表是无法在系统中看到的包括 show tables 也不能看到。 所以不能提前创建，只能动态创建
零时表只有session 级别的，没有事物级别的
A TEMPORARY table is visible only within the current session, and is dropped automatically when
the session is closed. This means that two different sessions can use the same temporary table
name without conflicting with each other or with an existing non-TEMPORARY table of the same
name. (The existing table is hidden until the temporary table is dropped.)
那么什么是mysql的session 呢？ session 是connection 建立成功后并且关联 THD 上下文的内存数据结构
A session is just a result of a successful connection.
Any MySQL client requires some connection settings to establish a connection, and after the
connection has been established,it acquires a connection id (thread id) and some context which
is called session.
参考mysql8.0.25 的源码注释 见下图
2. 创建和删除零时表不会提交事物
CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is
used. (This does not apply to other operations on temporary tables such as ALTER TABLE and
CREATE INDEX, which do cause a commit.) However, although no implicit commit occurs, neither
can the statement be rolled back, which means that the use of such statements causes
transactional atomicity to be violated. For example, if you use CREATE TEMPORARY TABLE and then
roll back the transaction, the table remains in existence.
一般来说DML语句是隐射提交事物的，但是创建和删除零时表不会提交事物
在事物中 创建一个零时表后，如果事物回滚后并不能删除零时表，所以要结合自己的程序显示 drop temporary table
3. 举个例子
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demo |
| demo2 |
| sys_log |
+----------------+
3 rows in set (0.00 sec)
mysql> CREATE TEMPORARY TABLE temp_demo2 ( `id` VARCHAR(50) NOT NULL,`name` VARCHAR(50) NULL DEFAULT NULL);
Query OK, 0 rows affected (0.00 sec)
-- 用show tables 看不到零时表 说明零时表不属于 当前数据库
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demo |
| demo2 |
| sys_log |
+----------------+
3 rows in set (0.00 sec)
mysql> select * from temp_demo2;
Empty set (0.00 sec)
mysql> insert into temp_demo2 values('1','222');
Query OK, 1 row affected (0.00 sec)
mysql> select * from temp_demo2;
+----+------+
| id | name |
+----+------+
| 1 | 222 |
+----+------+
另启动一个窗口(新会话),查询发现找不到零时表
mysql> use test;
Database changed
mysql> select * from temp_demo2;
ERROR 1146 (42S02): Table 'test.temp_demo2' doesn't exist
mysql>
4. 在程序中的应用零时表
一般来说在写统计报表时需要批量操作数据，借助零时表可以灵活实现功能
但是如何解决同一个 数据库连接池 创建零时表冲突问题
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Properties props = new Properties();
props.put("user", "test");
props.put("password", "123456");
String url = "jdbc:mysql://localhost:8024/test";
// 每次获取新的连接,测试是否负载均衡
try (Connection conn = DriverManager.getConnection(url, props)) {
conn.setAutoCommit(false);
Statement st = conn.createStatement();
//开始的时候创建，不要先删除后创建，这样会导致问题
//https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html
//创建零时表和删除零时表不会显式提交事物，但是非零时表会提交事物
//CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used
st.execute("CREATE TEMPORARY TABLE temp_demo (id VARCHAR(50),name VARCHAR(50))");
PreparedStatement pst = conn.prepareStatement("insert into temp_demo values(?,?)");
for (int i = 0; i < 5; i++) {
pst.setString(1, String.valueOf(i));
pst.setString(2, LocalDateTime.now().toString());
pst.execute();
}
pst.close();
pst = conn.prepareStatement("insert into demo values(?,?)");
for (int i = 0; i < 5; i++) {
pst.setString(1, String.valueOf(i));
pst.setString(2, LocalDateTime.now().toString());
pst.execute();
}
//conn.commit();
// 离开方法的时候删除,DROP TEMPORARY TABLE不会隐式提交数据
//DROP TABLE causes an implicit commit, except when used with the TEMPORARY keyword.
st.execute("DROP TEMPORARY TABLE IF EXISTS temp_demo");
// 利用当前连接重新创建表，如果没有删除，Table 'temp_demo' already exists
//st.execute("CREATE TEMPORARY TABLE temp_demo (id VARCHAR(50),name VARCHAR(50))");
ResultSet rs = st.executeQuery("select count(*) from temp_demo");
if (rs.next()) {
System.out.println(rs.getInt(1));
}
rs.close();
st.close();
pst.close();
} // end try
}
