CREATE OR REPLACE TABLE t1 (
|
c_tinytext tinytext,
|
c_text text,
|
c_mediumtext mediumtext,
|
c_longtext longtext
|
) CHARACTER SET utf8mb4;
|
|
CREATE OR REPLACE TABLE t2 AS SELECT
|
COALESCE(c_tinytext, CAST('binary data' AS BINARY)) AS mix_tinytext_binary,
|
COALESCE(c_text, CAST('binary data' AS BINARY)) AS mix_text_binary,
|
COALESCE(c_mediumtext, CAST('binary data' AS BINARY)) AS mix_mediumtext_binary,
|
COALESCE(c_longtext, CAST('binary data' AS BINARY)) AS mix_longtext_binary
|
FROM t1;
|
|
SHOW CREATE TABLE t2;
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`mix_tinytext_binary` blob DEFAULT NULL,
|
`mix_text_binary` mediumblob DEFAULT NULL,
|
`mix_mediumtext_binary` longblob DEFAULT NULL,
|
`mix_longtext_binary` longblob DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Some data types in t2 are wrong:
- mix_tinytext_binary is expected to be tinyblob instead of blob
- mix_text_binary is expected to be blob instead of mediumblob
- mix_medium_binary is expected to be mediumblob instead of longblob
- mix_longtext_binary is fine (longblob)
The same problem is repeatable when mixing for UNION:
CREATE OR REPLACE TABLE t2 AS SELECT
|
c_tinytext AS mix_tinytext_binary,
|
c_text AS mix_text_binary,
|
c_mediumtext AS mix_mediumtext_binary,
|
c_longtext AS mix_longtext_binary
|
FROM t1
|
UNION SELECT
|
CAST('binary data' AS BINARY),
|
CAST('binary data' AS BINARY),
|
CAST('binary data' AS BINARY),
|
CAST('binary data' AS BINARY);
|
|
SHOW CREATE TABLE t2;
|