MySQL生成数据库设计表格

65 阅读 0 评论 43 点赞

我是靠谱客的博主重要蛋挞，最近开发中收集的这篇文章主要介绍MySQL生成数据库设计表格，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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

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]下修改或增加