数据库表结构导出到Excel中

数据库导出表结构sql语句:

select column_name as 字段类型,
	 column_type as 数据类型,
	 data_type as 字段类型,
	 character_maximum_length as 长度,
	 is_nullable as 是否为空,
	 column_default as 默认值,
	 column_comment as 备注
from
	 information_schema.columns
where
	 table_schema = ‘数据库名称‘ and table_name = ‘表名称‘

查询出表结构,可以点击导出结果将查询的结果导出

也可以:

select column_name as 字段类型,
	 column_type as 数据类型,
	 data_type as 字段类型,
	 character_maximum_length as 长度,
	 is_nullable as 是否为空,
	 column_default as 默认值,
	 column_comment as 备注
from
	 information_schema.columns
where
	 table_schema = ‘数据库名称‘ and table_name = ‘表名称‘
	 into outfile ‘C:/Users/Admintor/Desktop/abc.xls‘   -- 导出到桌面的Excel表格

执行sql语句

如果执行这个sql语句报错时:--secure-file-priv
mysql限制了导入与导出的目录权限 需要修改mysql.ini配置文件

进入mysql执行show variables like ‘%secure%‘; 发现secure_file_priv路径为NULL
修改mysql.ini中,添加一句
secure-file-priv = C:/Users/权焕旭/Desktop/

重启MySQL,然后再次查看当前的secure-file-priv
show variables like ‘%secure%‘;

然后执行sql语句 可以正常导出

数据库表结构导出到Excel中

上一篇:oracle id 自增


下一篇:MySQL触发器trigger