|
This script:
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
|
@c1:=c1 AS c1,
|
@c2:=c2 AS c2,
|
@c3:=c3 AS c3,
|
@c4:=c4 AS c4,
|
@c5:=c5 AS c5,
|
@c6:=c6 AS c6,
|
@c7:=c7 AS c7 FROM t1;
|
SHOW CREATE TABLE t2;
|
returns
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c1` longblob,
|
`c2` longblob,
|
`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 run the same SELECT query that was used in CREATE TABLE t2:
SELECT
|
@c1:=c1 AS c1,
|
@c2:=c2 AS c2,
|
@c3:=c3 AS c3,
|
@c4:=c4 AS c4,
|
@c5:=c5 AS c5,
|
@c6:=c6 AS c6,
|
@c7:=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`
|
Catalog: `def`
|
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 column type in metadata does not match the column type in t2.
|