mysql数据库表结构导出到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表格,将查询的数据全选后右击选择复制为-制表符分隔值(栏位名和数据),复制到Excel表格中