workbench 批量导入sql文报错的几点记录

最新推荐文章于 2025-04-07 09:42:06 发布

原创最新推荐文章于 2025-04-07 09:42:06 发布 · 2.3k 阅读

1 ·

CC 4.0 BY-SA版权

MYSQL 专栏收录该内容

12 篇文章

订阅专栏

本文详细记录了在使用mysql.exe导入SQL文件时遇到的三种常见错误及其解决方案，包括'NO_AUTO_CREATE_USER'设置问题、函数声明问题以及本地主机名引用问题。通过修改SQL文件中的特定设置和语法，成功解决了导入过程中的错误。

导入：
11:47:11 Restoring D:\project\XXXXXXXX.sql
Running: mysql.exe --defaults-file="c:\users\sam\appdata\local\temp\tmpt6aeix.cnf" --protocol=tcp --host=192.168.9.28 --user=root --port=3306 --default-character-set=utf8 --comments < "D:\\project\\天天SRM\\trunk\\04代码管理\\01后台服务端\\tterp\\db\\20190817\\Dump20190817.sql"
ERROR 1231 (42000) at line 2116: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

Operation failed with exitcode 1
11:47:42 Import of D:\project\XXXXXXXX.sql has finished with 1 errors

解决方案：'NO_AUTO_CREATE_USER' 替换为空

11:48:35 Restoring D:\project\XXXXXXXXsql
Running: mysql.exe --defaults-file="c:\users\sam\appdata\local\temp\tmprhklvr.cnf" --protocol=tcp --host=192.168.9.28 --user=root --port=3306 --default-character-set=utf8 --comments < "D:\\project\\XXXXXXXX.sql"
ERROR 1418 (HY000) at line 2118: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Operation failed with exitcode 1
11:49:08 Import of D:\project\XXXXXXXX.sql has finished with 1 errors
解决方案：'locahost' 替换为ip地址

11:43:04 Restoring D:\project\XXXXXXXX.sql
Running: mysql.exe --defaults-file="c:\users\sam\appdata\local\temp\tmprj9etu.cnf" --protocol=tcp --host=192.168.9.28 --user=root --port=3306 --default-character-set=utf8 --comments < "D:\\project\\XXXXXXXX.sql"
ERROR 1418 (HY000) at line 5593: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Operation failed with exitcode 1
12:00:08 Import of D:\XXXXXXXX.sql has finished with 1 errors

解决方案：function begin 前添加 DETERMINISTIC