使用查询脚本生成建表语句

使用information_schema 视图名称columns,table_constraints通过查询用脚本生成建表语句，
此处的表必须是数据库存在的表，若是数据库不存在例如：user表查询结果则为空

SELECT 'create table user ('t_user1
union ALL
SELECT cols.txt
FROM 
(SELECT CONCAT(' ',column_name,' ',column_type,
CASE
when is_nullable='NO' THEN ' not null'
ELSE ''
END,
CASE
WHEN extra IS NOT NULL THEN CONCAT(' ',extra)
ELSE ''
END,
',') txt
FROM information_schema.COLUMNS
WHERE table_name='user'
ORDER BY ordinal_position
) cols
union ALL
SELECT CONCAT('  constraint primary key (')
FROM information_schema.TABLE_CONSTRAINTS 
WHERE table_name='user' 
AND CONSTRAINT_TYPE='PRIMARY KEY'
union ALL
SELECT cols.txt
FROM (
SELECT CONCAT(CASE WHEN ordinal_position>1 then '  ,'
ELSE '  ' end, column_name) txt
FROM  information_schema.KEY_COLUMN_USAGE
WHERE table_name='user'
AND CONSTRAINT_NAME='PRIMARY'
ORDER BY ORDINAL_POSITION
) cols
union ALL
SELECT ')'
UNION ALL
SELECT ')';