|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (
|
c1 LONGBLOB,
|
c2 MEDIUMBLOB,
|
c3 BLOB,
|
c4 TINYBLOB,
|
c5 VARCHAR(64),
|
c6 VARCHAR(4000),
|
c7 CHAR(64));
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 AS SELECT
|
MAX(COALESCE(c1)) AS c1,
|
MAX(COALESCE(c2)) AS c2,
|
MAX(COALESCE(c3)) AS c3,
|
MAX(COALESCE(c4)) AS c4,
|
MAX(COALESCE(c5)) AS c5,
|
MAX(COALESCE(c6)) AS c6,
|
MAX(COALESCE(c7)) AS c7 FROM t1;
|
SHOW CREATE TABLE t2;
|
returns
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c1` longblob,
|
`c2` mediumblob,
|
`c3` blob,
|
`c4` varbinary(255) DEFAULT NULL,
|
`c5` varchar(64) DEFAULT NULL,
|
`c6` text,
|
`c7` varchar(64) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Now if I run "mysql --column-type-info test" and execute this query:
SELECT
|
MAX(COALESCE(c1)) AS c1,
|
MAX(COALESCE(c2)) AS c2,
|
MAX(COALESCE(c3)) AS c3,
|
MAX(COALESCE(c4)) AS c4,
|
MAX(COALESCE(c5)) AS c5,
|
MAX(COALESCE(c6)) AS c6,
|
MAX(COALESCE(c7)) AS c7 FROM t1;
|
it returns the following metadata:
Field 1: `c1`
|
Type: LONG_BLOB
|
Collation: binary (63)
|
Length: 4294967295
|
|
Field 2: `c2`
|
Type: MEDIUM_BLOB
|
Collation: binary (63)
|
Length: 16777215
|
|
Field 3: `c3`
|
Type: VAR_STRING
|
Collation: binary (63)
|
Length: 65535
|
|
Field 4: `c4`
|
Type: VAR_STRING
|
Collation: binary (63)
|
Length: 255
|
|
Field 5: `c5`
|
Type: VAR_STRING
|
Collation: utf8_general_ci (33)
|
Length: 192
|
|
Field 6: `c6`
|
Type: VAR_STRING
|
Collation: utf8_general_ci (33)
|
Length: 12000
|
|
Field 7: `c7`
|
Type: VAR_STRING
|
Collation: utf8_general_ci (33)
|
Length: 192
|
Notice, for some columns the type of the column created in CREATE TABLE...SELECT does not match the type of the column returned in the metadata for the same SELECT query.
|