MySQL生成数据库设计表格

#MySQL生成数据库设计表格| 来源: 网络整理| 查看: 265

查询单个指定数据库和数据库表

SET @db_name = 'test'; SET @table_name = 'sys_user'; SELECT COLUMN_NAME 字段名称, COLUMN_TYPE 字段类型, IF ( COLUMN_NAME IN ( SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE AS T WHERE T.table_schema = @db_name AND T.table_name = @table_name ), 'PRIMARY KEY', IF ( IS_NULLABLE = 'NO', 'NOT NULL', 'NULL' ) ) AS 字段约束, COLUMN_COMMENT 字段注释 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = @db_name AND table_name = @table_name;

查询多个指定数据库

SET @db_name = 'test'; SELECT CONCAT( 'SELECT COLUMN_NAME 字段名称, COLUMN_TYPE 字段类型, IF ( COLUMN_NAME IN ( SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE AS T WHERE T.table_schema = \'', @db_name, '\' AND T.table_name = \'', table_name, '\' ), \'PRIMARY KEY\', IF ( IS_NULLABLE = \'NO\', \'NOT NULL\', \'NULL\' ) ) AS 字段约束, COLUMN_COMMENT 字段注释 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = \'', @db_name, '\' AND table_name = \'', table_name, '\';' ) FROM information_schema.TABLES WHERE table_schema = @db_name;

但是还不够方便如下为使用EXECUTE执行GROUP_CONCAT拼接的OUTFILE语句直接输出到文件 PS: 搞了好长时间主要是修改配置文件和一些报错

SET @db_name = 'test'; SELECT @SQL := CONCAT( 'SELECT * INTO OUTFILE \'/data/all.xlsx\' FIELDS TERMINATED BY \'\t\' FROM (', GROUP_CONCAT( CONCAT( 'SELECT \'表名 -> ', table_name, '\' AS 字段名称, \'描述 -> ',table_comment,'\' AS 字段类型, \'+\' AS 字段约束, \'-\' AS 字段注释 UNION ALL SELECT COLUMN_NAME, COLUMN_TYPE, IF ( COLUMN_NAME IN ( SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE AS T WHERE T.table_schema = \'', @db_name, '\' AND T.table_name = \'', table_name, '\' ), \'PRIMARY KEY\', IF ( IS_NULLABLE = \'NO\', \'NOT NULL\', \'NULL\' ) ), COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = \'', @db_name, '\' AND table_name = \'', table_name, '\'' ) SEPARATOR ' UNION ALL ' ), ') a' ) FROM information_schema.TABLES WHERE table_schema = @db_name # 此处条件可自行修改 AND table_name NOT LIKE '%act_%'; PREPARE _sql FROM @SQL; EXECUTE _sql;

如下配置生效要重启mysql服务 systemctl restart mysql

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 需要配置secure-file-priv 编辑mysql的my.cnf在[mysqld]修改或添加无[mysqld]请手动创建 secure-file-priv为null(默认值) 不允许导入|导出 secure_file_priv为/data 导入|导出只能发生在/tmp/目录下 secure_file_priv没有值表示不对导入|导出做限制

Can’t create/write to file ‘/data/text.csv’ (Errcode: 13 - Permission denied) 说明没有权限操作好像是上面的配置没有全部生效通过设置tmpdir可解决有更好的解决方式我在更新 tmpdir = /data # 同样是在[mysqld]下修改或增加

如果最后显示的sql语句不全可能是GROUP_CONCAT的限制太小有的人说设为-1为无限制但是我的还是显示不全 group_concat_max_len=99999 # 同样是在[mysqld]下修改或增加

Duplicate column name ‘xxxx’ 别名重复重复字段名称起个别名即可

结果图如下

【本文地址】

公司简介

联系我们