Code: Select all
-- Server 5.5.5-10.0.31-MariaDB-1~xenial
USE test;
CREATE TABLE tt (
TestID int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
t1 varchar(255) DEFAULT '',
t2 date DEFAULT NULL,
t3 varchar(255) DEFAULT 'smail',
t4 varchar(255) NOT NULL,
PRIMARY KEY (TestID)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci;
-- Server 5.5.5-10.2.7-MariaDB-10.2.7+maria~xenial-log
USE test;
CREATE TABLE tt (
TestID int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
t1 varchar(255) DEFAULT '''''',
t2 date DEFAULT 'NULL',
t3 varchar(255) DEFAULT '''smail''',
t4 varchar(255) NOT NULL,
PRIMARY KEY (TestID)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci
ROW_FORMAT = DYNAMIC;
--
SELECT
c.TABLE_NAME,
COLUMN_NAME,
COLUMN_TYPE,
IS_NULLABLE,
COLUMN_KEY,
COLUMN_DEFAULT,
EXTRA,
COLLATION_NAME,
CHARACTER_SET_NAME,
COLUMN_COMMENT
FROM information_schema.COLUMNS c
INNER JOIN (SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM information_schema.TABLES
WHERE LOWER(TABLE_SCHEMA) = LOWER('test')) t
ON t.TABLE_NAME COLLATE utf8_bin = c.TABLE_NAME
COLLATE utf8_bin
WHERE LOWER(c.TABLE_SCHEMA) = LOWER('test')
AND ('Y' = ''
OR LOWER(c.TABLE_NAME) IN ('tt'))
ORDER BY t.TABLE_NAME;