CREATE TABLE t1
(
t1_BIGINT BIGINT,
t1_BIGINT_UNSIGNED BIGINT UNSIGNED,
t1_BINARY_100 BINARY(100),
t1_BIT_2 BIT(2),
t1_BIT_20 BIT(20),
t1_BLOB BLOB,
t1_CHAR_100 CHAR(100),
t1_CHAR_100_BINARY CHAR(100) BINARY,
t1_DATE DATE,
t1_DATETIME DATETIME,
t1_DATETIME_6 DATETIME(6),
t1_DECIMAL_10_3 DECIMAL(10,3),
t1_DECIMAL_10_3_UNSIGNED DECIMAL(10,3) UNSIGNED,
t1_DOUBLE DOUBLE,
t1_DOUBLE_UNSIGNED DOUBLE UNSIGNED,
t1_ENUM       ENUM('a', 'b', 'c'),
t1_ENUM_BINARY ENUM('a','b') BINARY,
t1_ENUM_256    ENUM('a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
'a10', 'a11', 'a12', 'a13', 'a14', 'a15', 'a16', 'a17', 'a18', 'a19',
'a20', 'a21', 'a22', 'a23', 'a24', 'a25', 'a26', 'a27', 'a28', 'a29',
'a30', 'a31', 'a32', 'a33', 'a34', 'a35', 'a36', 'a37', 'a38', 'a39',
'a40', 'a41', 'a42', 'a43', 'a44', 'a45', 'a46', 'a47', 'a48', 'a49',
'a50', 'a51', 'a52', 'a53', 'a54', 'a55', 'a56', 'a57', 'a58', 'a59',
'a60', 'a61', 'a62', 'a63', 'a64', 'a65', 'a66', 'a67', 'a68', 'a69',
'a70', 'a71', 'a72', 'a73', 'a74', 'a75', 'a76', 'a77', 'a78', 'a79',
'a80', 'a81', 'a82', 'a83', 'a84', 'a85', 'a86', 'a87', 'a88', 'a89',
'a90', 'a91', 'a92', 'a93', 'a94', 'a95', 'a96', 'a97', 'a98', 'a99',
'a100', 'a101', 'a102', 'a103', 'a104', 'a105', 'a106', 'a107', 'a108',
'a109', 'a110', 'a111', 'a112', 'a113', 'a114', 'a115', 'a116', 'a117',
'a118', 'a119', 'a120', 'a121', 'a122', 'a123', 'a124', 'a125', 'a126',
'a127', 'a128', 'a129', 'a130', 'a131', 'a132', 'a133', 'a134', 'a135',
'a136', 'a137', 'a138', 'a139', 'a140', 'a141', 'a142', 'a143', 'a144',
'a145', 'a146', 'a147', 'a148', 'a149', 'a150', 'a151', 'a152', 'a153',
'a154', 'a155', 'a156', 'a157', 'a158', 'a159', 'a160', 'a161', 'a162',
'a163', 'a164', 'a165', 'a166', 'a167', 'a168', 'a169', 'a170', 'a171',
'a172', 'a173', 'a174', 'a175', 'a176', 'a177', 'a178', 'a179', 'a180',
'a181', 'a182', 'a183', 'a184', 'a185', 'a186', 'a187', 'a188', 'a189',
'a190', 'a191', 'a192', 'a193', 'a194', 'a195', 'a196', 'a197', 'a198',
'a199', 'a200', 'a201', 'a202', 'a203', 'a204', 'a205', 'a206', 'a207',
'a208', 'a209', 'a210', 'a211', 'a212', 'a213', 'a214', 'a215', 'a216',
'a217', 'a218', 'a219', 'a220', 'a221', 'a222', 'a223', 'a224', 'a225',
'a226', 'a227', 'a228', 'a229', 'a230', 'a231', 'a232', 'a233', 'a234',
'a235', 'a236', 'a237', 'a238', 'a239', 'a240', 'a241', 'a242', 'a243',
'a244', 'a245', 'a246', 'a247', 'a248', 'a249', 'a250', 'a251', 'a252',
'a253', 'a254', 'a255', 'a256'), 
t1_FLOAT FLOAT,
t1_FLOAT_UNSIGNED FLOAT UNSIGNED,
t1_INT INT,
t1_INT_UNSIGNED INT UNSIGNED,
t1_LONGBLOB LONGBLOB,
t1_LONGTEXT LONGTEXT,
t1_MEDIUMBLOB MEDIUMBLOB,
t1_MEDIUMINT MEDIUMINT,
t1_MEDIUMINT_UNSIGNED MEDIUMINT UNSIGNED,
t1_MEDIUMTEXT MEDIUMTEXT,
t1_SET        SET('a', 'b', 'c'),
t1_SET_BINARY SET('a','b') BINARY,
t1_SET_9      SET('a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9'),
t1_SMALLINT SMALLINT,
t1_SMALLINT_UNSIGNED SMALLINT UNSIGNED,
t1_TEXT TEXT,
t1_TIME TIME,
t1_TIME_4 TIME(4),
t1_TIMESTAMP TIMESTAMP,
t1_TIMESTAMP_5 TIMESTAMP(5),
t1_TINYBLOB TINYBLOB,
t1_TINYINT  TINYINT,
t1_TINYINT_UNSIGNED TINYINT UNSIGNED,
t1_TINYTEXT TINYTEXT,
t1_VARBINARY_100 VARBINARY(100),
t1_VARCHAR_10 VARCHAR(10),
t1_VARCHAR_10_BINARY VARCHAR(10) BINARY,
t1_VARCHAR_500 VARCHAR(500),
t1_VARCHAR_500_BINARY VARCHAR(500) BINARY,
t1_YEAR_2 YEAR(2),
t1_YEAR_4 YEAR(4)
) ENGINE=InnoDB;
Warnings:
Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
SELECT
name,
CASE mtype
WHEN 1 THEN "DATA_VARCHAR"
    WHEN 2 THEN "DATA_CHAR"
    WHEN 3 THEN "DATA_FIXBINARY"
    WHEN 4 THEN "DATA_BINARY"
    WHEN 5 THEN "DATA_BLOB"
    WHEN 6 THEN "DATA_INT"
    WHEN 7 THEN "DATA_SYS_CHILD"
    WHEN 8 THEN "DATA_SYS"
    WHEN 9 THEN "DATA_FLOAT"
    WHEN 10 THEN "DATA_DOUBLE"
    WHEN 11 THEN "DATA_DECIMAL"
    WHEN 12 THEN "DATA_VARMYSQL"
    WHEN 13 THEN "DATA_MYSQL"
    WHEN 63 THEN "DATA_MTYPE_MAX"
    ELSE mtype
END AS mtype,
IF((prtype & 512) = 512,"UNSIGNED","") AS is_unsigned
FROM information_schema.INNODB_SYS_COLUMNS
WHERE name LIKE "t1\_%"
ORDER BY name;
name	mtype	is_unsigned
t1_BIGINT	DATA_INT	
t1_BIGINT_UNSIGNED	DATA_INT	UNSIGNED
t1_BINARY_100	DATA_FIXBINARY	
t1_BIT_2	DATA_FIXBINARY	UNSIGNED
t1_BIT_20	DATA_FIXBINARY	UNSIGNED
t1_BLOB	DATA_BLOB	
t1_CHAR_100	DATA_CHAR	
t1_CHAR_100_BINARY	DATA_MYSQL	
t1_DATE	DATA_INT	
t1_DATETIME	DATA_FIXBINARY	
t1_DATETIME_6	DATA_FIXBINARY	
t1_DECIMAL_10_3	DATA_FIXBINARY	
t1_DECIMAL_10_3_UNSIGNED	DATA_FIXBINARY	UNSIGNED
t1_DOUBLE	DATA_DOUBLE	
t1_DOUBLE_UNSIGNED	DATA_DOUBLE	UNSIGNED
t1_ENUM	DATA_INT	UNSIGNED
t1_ENUM_256	DATA_INT	UNSIGNED
t1_ENUM_BINARY	DATA_INT	UNSIGNED
t1_FLOAT	DATA_FLOAT	
t1_FLOAT_UNSIGNED	DATA_FLOAT	UNSIGNED
t1_INT	DATA_INT	
t1_INT_UNSIGNED	DATA_INT	UNSIGNED
t1_LONGBLOB	DATA_BLOB	
t1_LONGTEXT	DATA_BLOB	
t1_MEDIUMBLOB	DATA_BLOB	
t1_MEDIUMINT	DATA_INT	
t1_MEDIUMINT_UNSIGNED	DATA_INT	UNSIGNED
t1_MEDIUMTEXT	DATA_BLOB	
t1_SET	DATA_INT	UNSIGNED
t1_SET_9	DATA_INT	UNSIGNED
t1_SET_BINARY	DATA_INT	UNSIGNED
t1_SMALLINT	DATA_INT	
t1_SMALLINT_UNSIGNED	DATA_INT	UNSIGNED
t1_TEXT	DATA_BLOB	
t1_TIME	DATA_FIXBINARY	
t1_TIMESTAMP	DATA_FIXBINARY	UNSIGNED
t1_TIMESTAMP_5	DATA_FIXBINARY	UNSIGNED
t1_TIME_4	DATA_FIXBINARY	
t1_TINYBLOB	DATA_BLOB	
t1_TINYINT	DATA_INT	
t1_TINYINT_UNSIGNED	DATA_INT	UNSIGNED
t1_TINYTEXT	DATA_BLOB	
t1_VARBINARY_100	DATA_BINARY	
t1_VARCHAR_10	DATA_VARCHAR	
t1_VARCHAR_10_BINARY	DATA_VARMYSQL	
t1_VARCHAR_500	DATA_VARCHAR	
t1_VARCHAR_500_BINARY	DATA_VARMYSQL	
t1_YEAR_2	DATA_INT	UNSIGNED
t1_YEAR_4	DATA_INT	UNSIGNED
DROP TABLE t1;