用select语句来代替show create table来显示表结构

用select语句来代替show create table来简单显示表结构，此语句不包含创建表索引语句。

select语句查询表结构:

SET @db_name='aa';
SET @table_name='bb';
SELECT TABLE_NAME,
CONCAT('CREATE TABLE ','`',TABLE_NAME,'` (',GROUP_CONCAT(
"\r\n",
CONCAT(CONCAT('`',COLUMN_NAME,'`'),
' ',
COLUMN_TYPE,
' ',
IF(IS_NULLABLE='NO','NOT NULL',''),
 IF(COLUMN_TYPE='text','',IF(COLUMN_DEFAULT IS NULL AND IS_NULLABLE='NO','',CONCAT(' DEFAULT ',IF(COLUMN_DEFAULT IS NULL,'NULL',CONCAT('\'',COLUMN_DEFAULT,'\'')))))),
IF(EXTRA='','',' auto_increment')
),
CONCAT(",\r\n",'PRIMARY KEY (`',(SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME=@table_name AND COLUMN_KEY='PRI'),'`)',"\r\n",')'),
(SELECT CONCAT(' ENGINE=',ENGINE,' DEFAULT CHARSET=',SUBSTRING(TABLE_COLLATION,1,LOCATE('_',TABLE_COLLATION)-1)) FROM information_schema.TABLES WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME=@table_name)
) AS TABLE_SCHEMA
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db_name AND TABLE_NAME=@table_name;

话说这个改改貌似可以用来注入了。。

不过这个还是有局限性的，group_concat长度一般默认为1024，所以对于结构太复杂的表可能会发生截断，显示不完整。