mysql数据库导出表结构为 Excel

1. 打开 Navicat, 新建查询, 执行下列语句查询 MySQ L数据库表结构信息,复制到 Excel, 后续你可以自己处理分组:

SELECT
	CONCAT_WS(' : ',t.table_name,t.table_comment) AS '表名',
	sc.column_name AS '字段名称',
	sc.column_type AS '数据类型',
	sc.data_type AS '字段类型',
	sc.character_maximum_length AS '长度',
	sc.is_nullable AS '是否为空',
	sc.column_default AS '默认值',
	sc.column_comment AS '备注',
	sc.column_key AS '约束',
	c.referenced_table_name AS '父表名称',
  c.referenced_column_name AS '父表字段',
  c.constraint_name AS '约束名'
FROM
	information_schema.COLUMNS sc JOIN information_schema.TABLES t ON sc.table_name = t.TABLE_NAME JOIN information_schema.KEY_COLUMN_USAGE c ON t.TABLE_NAME = c.table_name
WHERE
	sc.table_schema = '数据库名称'

2. 也可以开启mysql导入与导出的目录权限, 打开mysql.cnf配置文件,改为:

secure-file-priv = '/tmp/目录'

如果没有 my.conf ,终端登录管理员,

vim /private/etc/my.cnf

写入:
[mysqld]
secure_file_priv='/tmp/mysql_out'

secure_file_prive说明:

secure_file_prive=null， //不允许导入导出
secure_file_priv=目录，//只能在指定目录下导入导出
secure_file_priv=' '，//不对导入导出做限制

另外mac下要把目录设在'tmp'下, 不然MySQL要么报错要么无法重启,
还要给mysql 用户写入权限,
例如 :
mysql.cnf 设置目录:  secure-file-priv = '/tmp/mysql_out'
赋权: chown _mysql:_mysql /private/tmp/mysql_out
然后系统偏好设置里重启 MySQL 之后, 执行:
SHOW VARIABLES LIKE "secure_file_priv";

看到设置目录即为成功

然后执行下列语句即可 直接导出 xls

SELECT
	CONCAT_WS(' : ',t.table_name,t.table_comment) AS '表名',
	sc.column_name AS '字段名称',
	sc.column_type AS '数据类型',
	sc.data_type AS '字段类型',
	sc.character_maximum_length AS '长度',
	sc.is_nullable AS '是否为空',
	sc.column_default AS '默认值',
	sc.column_comment AS '备注',
	sc.column_key AS '约束',
	c.referenced_table_name AS '父表名称',
  c.referenced_column_name AS '父表字段',
  c.constraint_name AS '约束名'
FROM
	information_schema.COLUMNS sc JOIN information_schema.TABLES t ON sc.table_name = t.TABLE_NAME JOIN information_schema.KEY_COLUMN_USAGE c ON t.TABLE_NAME = c.table_name
WHERE
	sc.table_schema = 'meditation' INTO OUTFILE '导出到哪个目录/XXX.xls'