如何永久禁用 MySQL 8 中的列统计信息？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/51614140/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to disable column-statistics in MySQL 8 permanently?
提问by Flo Bayer
Since MySQL 8the column-statisticsflag is enabled by default.
从MySQL 8 开始，该column-statistics标志默认启用。
So if you try to dump some tables with MySQL Workbench8.0.12, you get this error message:
因此，如果您尝试使用MySQL Workbench8.0.12转储某些表，则会收到以下错误消息：
14:50:22 Dumping db (table_name) Running: mysqldump.exe --defaults-file="c:\users\username\appdata\local\temp\tmpvu0mxn.cnf" --user=db_user --host=db_host --protocol=tcp --port=1337 --default-character-set=utf8 --skip-triggers "db_name" "table_name" mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'db_name' AND TABLE_NAME = 'table_name';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
Operation failed with exitcode 2 14:50:24 Export of C:\path\to\my\dump has finished with 1 errors
14:50:22 转储 db (table_name) 运行：mysqldump.exe --defaults-file="c:\users\username\appdata\local\temp\tmpvu0mxn.cnf" --user=db_user --host=db_host - -protocol=tcp --port=1337 --default-character-set=utf8 --skip-triggers "db_name" "table_name" mysqldump: 无法执行 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of -buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'db_name' AND TABLE_NAME = 'table_name';': information_schema 中的未知表 'COLUMN_STATISTICS' (1109)
操作失败，退出代码 2 14:50:24 导出 C:\path\to\my\dump has completed with 1 errors
Is there any way in MySQL (Workbench) 8 to disable column-statisticspermanently?
MySQL (Workbench) 8 中是否有任何方法可以column-statistics永久禁用？
Workaround 1
解决方法 1
An annoying workaround is doing it by hand via:
一个烦人的解决方法是通过以下方式手动完成：
mysqldump --column-statistics=0 --host=...
Workaround 2
解决方法 2
- rename mysqldump
- create a shell script(or batch on Windows)
- call the renamed mysqldump with the
--column-statistics=0argument within this script - save it as mysqldump
- 重命名mysqldump
- 创建一个shell 脚本（或在 Windows 上批处理）
- 使用
--column-statistics=0此脚本中的参数调用重命名的 mysqldump - 将其保存为 mysqldump
Workaround 3
解决方法 3
- download MySQL 5.7
- extract mysqldump
- use this mysqldump
- 下载MySQL 5.7
- 提取mysqldump
- 使用这个 mysqldump
For example in MySQL Workbench: Edit / Preferences... / Administration / Path to mysqldump Tool
例如在 MySQL Workbench 中：Edit / Preferences... / Administration / Path to mysqldump Tool
Thanks in advance!
提前致谢！
回答by user3175253
Workaround for me:
我的解决方法：
- Create file named mysqldump.cmd with contents:
- 使用内容创建名为 mysqldump.cmd 的文件：
@echo off
"c:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump.exe" %* --column-statistics=0
(replace path to mysqldump.exe if necessary)
（如有必要，替换 mysqldump.exe 的路径）
- Open MySQL Workbench and go to Edit > Preferences > Administration, change path to mysqldump tool and point it to mysqldump.cmd
- 打开 MySQL Workbench 并转到 Edit > Preferences > Administration，将路径更改为 mysqldump 工具并将其指向 mysqldump.cmd
回答by Troyd
Easiest Work Around
最简单的工作
When using Mysql Workbench 8.0
使用Mysql Workbench 8.0 时
- Open the "Data Export" Tab
- Click Advanced Options
- Under the Other heading, set column statistics to 0
- Export again
- 打开“数据导出”选项卡
- 单击高级选项
- 在其他标题下，将列统计信息设置为 0
- 再次导出
Best of luck!
祝你好运！
回答by Mike Lischke
The idea is this: each server version has a dedicated mysqldump version. Not ideal and certainly not very backwards compatible, but that's the situation. MySQL Workbench can only include one mysqldump binary, so the latest one is used. The approach to download the MySQL 5.7 zip and use mysqldump from there is a good workaround without many side effects. You only have to be careful which server you dump with which dump version.
这个想法是这样的：每个服务器版本都有一个专用的 mysqldump 版本。不理想，当然也不是很向后兼容，但情况就是这样。MySQL Workbench 只能包含一个 mysqldump 二进制文件，因此使用最新的。从那里下载 MySQL 5.7 zip 并使用 mysqldump 的方法是一个很好的解决方法，没有很多副作用。您只需要小心使用哪个转储版本转储哪个服务器。
If you like to have that column stat flag automatically applied by MySQL Workbench please file a bug report at https://bugs.mysql.com.
如果您希望 MySQL Workbench 自动应用该列统计标志，请在https://bugs.mysql.com提交错误报告。
Update
更新
Meanwhile a bug report has been created for this issue: https://bugs.mysql.com/bug.php?id=91640
同时为这个问题创建了一个错误报告：https: //bugs.mysql.com/bug.php?id=91640
回答by user2363969
I have download the version 8.0.16, still the same issue.
我已经下载了 8.0.16 版本，还是同样的问题。
At data export advanced options I didn't saw any option about "statistics"! I have add at my.ini at
在数据导出高级选项中，我没有看到任何关于“统计”的选项！我在 my.ini 添加了
[mysqldump]
quick
max_allowed_packet = 16M
column-statistics=0
Doesn't solve the issue
不解决问题
I have been googling but I couldn't find the solution. I find a of companions who are strugling with this but not anyone with the solution at least for me.
我一直在谷歌搜索，但我找不到解决方案。我找到了一些正在为此苦苦挣扎的同伴，但至少对我来说没有找到解决方案的人。
At the end I have changed at MySQL Workbench file c:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules\wb_admin_export.py:
最后，我更改了 MySQL Workbench 文件 c:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules\wb_admin_export.py：
skip_column_statistics = True #if get_mysqldump_version() > Version(8, 0, 2) and self.owner.ctrl_be.target_version < Version(8, 0, 0) else False
I have made comment of
我已发表评论
if get_mysqldump...
I know this is not the way, but I can't find now another better solution. For me is this at least weird and strange. If anyone has a better solution let me know!
我知道这不是办法，但我现在找不到另一个更好的解决方案。对我来说，这至少是奇怪和奇怪的。如果有人有更好的解决方案，请告诉我！
回答by AppSol
There may be another workaround if you are running an OS that supports mysqldump (i.e. Linux). Set the path to the mysqldump binary in the preferences and include the --column-statistics=0 argument in the path, in: Edit >> Preferences >> Administration >> path to MysqlDump Tool
如果您运行的操作系统支持 mysqldump（即 Linux），则可能还有另一种解决方法。在首选项中设置 mysqldump 二进制文件的路径，并在路径中包含 --column-statistics=0 参数，在：Edit >> Preferences >> Administration >> path to MysqlDump Tool
回答by JCT1991
WINDOWS USERS
WINDOWS 用户
I have MySQL Workbench 8.0CE and Xampp v3.2.3and this worked for me:
我有MySQL Workbench 8.0CE 和 Xampp v3.2.3，这对我有用：
- Open xampp, and open mysql config > my.ini // add: column-statistics = 0 so:
- 打开 xampp，然后打开 mysql config > my.ini // 添加：column-statistics = 0 所以：
[mysqldump]
quick
max_allowed_packet = 16M
column-statistics = 0
Save and close, reboot xampp mysql server. (just in case)
保存并关闭，重启 xampp mysql 服务器。（以防万一）
in Workbench: Edit > Preferences > Administration
在 Workbench 中： 编辑 > 首选项 > 管理
In Mysqldump route put your mysqldump route of xampp, in my case:
在 Mysqldump 路由中放置 xampp 的 mysqldump 路由，在我的情况下：
C:\xampp\mysql\bin\mysqldump.exe
This worked for me!
这对我有用！
回答by SharpC
回答by Baptiste
Download the last version of Mysql Workbench 8.0.16 and no more problems.
下载 Mysql Workbench 8.0.16 的最新版本，没有更多问题。
https://dev.mysql.com/downloads/workbench/
https://dev.mysql.com/downloads/workbench/
No option to check !
没有选项可以检查！
![MySQL SQLSTATE[01000]：警告：1265 列的数据被截断](/res/img/loading.gif)