SQL中使用SELECT语句替代SHOW语句
制作代码生成器的时候需要读取表相关数据，但是使用show语句查询的时候信息不完整，返回字段是动态的，不便于绑定bean，研究了下Mysql相关网站换了种查询方式。
SHOW TABLES 替换为 select * from information_schema.tables where TABLE_SCHEMA = ‘数据库名’
mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | user | +----------------+ 1 row in set
mysql> select * from information_schema.tables where TABLE_SCHEMA = 'test'; +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ | def | test | user | BASE TABLE | InnoDB | 10 | Compact | 6 | 2730 | 16384 | 0 | 0 | 0 | NULL | 2018-07-27 10:28:02 | NULL | NULL | utf8_general_ci | NULL | | | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ 1 row in set
酱紫就可以查询到更多的表信息
SHOW DATABASES 替换为 select * from information_schema.SCHEMATA
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | XXX | | XXX | | XXX | | XXX | | XXX | | mysql | | XXX | | XXX | | test | | XXX | | XXX | +--------------------+ 12 rows in set
mysql> select * from information_schema.SCHEMATA; +--------------+--------------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+--------------------+----------------------------+------------------------+----------+ | def | information_schema | utf8 | utf8_general_ci | NULL | | def | XXX | utf8 | utf8_general_ci | NULL | | def | XXX | utf8 | utf8_general_ci | NULL | | def | XXX | utf8 | utf8_general_ci | NULL | | def | XXX | utf8 | utf8_general_ci | NULL | | def | XXX | utf8 | utf8_general_ci | NULL | | def | mysql | utf8 | utf8_general_ci | NULL | | def | XXX | utf8 | utf8_general_ci | NULL | | def | XXX | utf8 | utf8_general_ci | NULL | | def | test | utf8 | utf8_general_ci | NULL | | def | XXX | utf8 | utf8_general_ci | NULL | | def | XXX | utf8 | utf8_general_ci | NULL | +--------------+--------------------+----------------------------+------------------------+----------+ 12 rows in set
DESC 替换为 select * from information_schema.columns where table_schema = ‘数据库名’ and table_name = ‘表名’ ;
mysql> desc user; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(10) | YES | | NULL | | | isman | tinyint(1) | YES | | NULL | | | address | varchar(255) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ 4 rows in set
mysql> select * from information_schema.columns where table_schema = 'test' and table_name = 'user' ; +---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | +---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+ | def | test | user | id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | PRI | | select,insert,update,references | | | def | test | user | name | 2 | NULL | YES | varchar | 10 | 30 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(10) | | | select,insert,update,references | | | def | test | user | isman | 3 | NULL | YES | tinyint | NULL | NULL | 3 | 0 | NULL | NULL | NULL | tinyint(1) | | | select,insert,update,references | | | def | test | user | address | 4 | NULL | YES | varchar | 255 | 765 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(255) | | | select,insert,update,references | | +---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+ 4 rows in set
