如何在 mariadb 或 mysql 中创建临时表?

2022-01-15 00:00:00 mariadb mysql database-migration sql-server

现在，我正在将 mssql 迁移到 mariadb(mysql).mssql有with子句(with A as ())，但是mysql没有with子句.所以我使用创建临时表"，但出现了一些错误.

Now, I'm migrating mssql to mariadb(mysql). Mssql has with clause(with A as ()), but mysql has not with clause. So I'm using "create temporary table", but some error occurred.

例子，

CREATE TEMPORARY TABLE TMP(
    SELECT CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %T.%f') AS CHAR(19)) AS REGDT
);

这是错误代码

MySQL 数据库错误:截断不正确的 CHAR(19) 值:'2016-06-27 10:34:34.000000'

MySQL Database Error: Truncated incorrect CHAR(19) value: '2016-06-27 10:34:34.000000'

怎么了?

这是我的查询.

CREATE TEMPORARY TABLE TMP(
  SELECT DEPTCODE, DEPTNAME, USEYN, HIGHDEPTCODE, 
         CAST(DATE_FORMAT(REGDT, '%Y-%m-%d %T.%f') AS CHAR(19)) AS REGDT,
         0 AS LEV, CAST(DEPTCODE AS CHAR(255)) AS SORT, DEPTORDER 
    FROM TBDEPARTMENT
   WHERE DEPTCODE = '1000');

推荐答案

CHAR(19) 太小，无法以毫秒为单位存储整个日期.

CHAR(19) is too small to store your whole date with milliseconds.

改用 CHAR(26):

Use CHAR(26) instead:

CREATE TEMPORARY TABLE TMP(
    SELECT CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %T.%f') AS CHAR(26)) AS REGDT
);

或删除毫秒:

CREATE TEMPORARY TABLE TMP(
    SELECT CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %T') AS CHAR(19)) AS REGDT
);

您也可以使用 DATETIME 类型而不是字符串.

You could also use a DATETIME type instead of a String.

相关文章