中止 MySQL 脚本执行的方法（可能引发错误）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/773889/
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
Way to abort execution of MySQL scripts (raising error perhaps)?
提问by StaxMan
I need to write setup scripts for MySQL (usually run using 'source [file]' from mysql console) that depend partly on existing data, and there are differences in environments, meaning that sometimes script does fail. A common case is that a 'SET' statement with a select (to locate an id) fails to find anything; console sets value to NULL. In this case I would like the script to fail; I have heard this would be done by raising an error (with other DBs). However it appears like MySQL didn't have a way to do this.
我需要为 MySQL 编写安装脚本（通常使用来自 mysql 控制台的“源 [文件]”运行），该脚本部分依赖于现有数据，并且环境存在差异，这意味着有时脚本会失败。一个常见的情况是带有选择（定位 id）的“SET”语句找不到任何东西；控制台将值设置为 NULL。在这种情况下，我希望脚本失败；我听说这可以通过引发错误（与其他数据库）来完成。然而，MySQL 似乎没有办法做到这一点。
Are there good ways to force failure under such conditions? As things are now updates will fail when insert tries to use null, but even that does not terminate script itself. Ideally it would just fail and terminate as soon as a problem is encountered.
在这种情况下，是否有强制失败的好方法？现在，当 insert 尝试使用 null 时，更新将失败，但即使这样也不会终止脚本本身。理想情况下，一旦遇到问题，它就会失败并终止。
采纳答案by zombat
I think what you're encountering is a limitation of the MySQL console. Given a list of statements, the MySQL console executes each one regardless of any errors generated. Even if you implemented some of the error-raising suggestions that previous comments have mentioned, the MySQL console won't stop executing when such an error is encountered.
我认为您遇到的是 MySQL 控制台的限制。给定一个语句列表，MySQL 控制台会执行每个语句，而不管生成的任何错误。即使您实现了之前评论中提到的一些引发错误的建议，当遇到此类错误时，MySQL 控制台也不会停止执行。
I'll assume that you don't have the resources to apply a scripting language to the problem that could execute your SQL for you and handle the errors. I think in this case, you just need a more robust tool than the MySQL console.
我假设您没有资源将脚本语言应用于可以为您执行 SQL 并处理错误的问题。我认为在这种情况下，您只需要一个比 MySQL 控制台更强大的工具。
MySQL Administratordoes what you need, if I understand your problem correctly. If you set up your MySQL connection and connect to the database, you have two tools available from the Tools menu. The normal MySQL console is there, but you also have the MySQL Query Browser.
如果我正确理解您的问题，MySQL Administrator会满足您的需求。如果您设置了 MySQL 连接并连接到数据库，则可以从“工具”菜单中使用两个工具。普通的 MySQL 控制台就在那里，但您也有 MySQL 查询浏览器。
If you open the Query Browser, you get a decent GUI view of your MySQL databases. File -> Open Script to open your SQL script, then use the Execute button.
如果您打开查询浏览器，您将获得一个不错的 MySQL 数据库 GUI 视图。File -> Open Script 打开您的 SQL 脚本，然后使用 Execute 按钮。
You get a nice progress bar, and more importantly from the sounds of it, if a query fails, the script execution halts and highlights the failed query. You can choose to skip it and keep going, or even manually modify your data and start up from someplace else farther down the script.
您会得到一个不错的进度条，更重要的是从它的声音中可以看出，如果查询失败，脚本执行将停止并突出显示失败的查询。您可以选择跳过它并继续，或者甚至手动修改您的数据并从脚本更远的其他地方启动。
I abandoned the MySQL console almost immediately once I found out about and tried Administrator.
一旦我发现并尝试了管理员，我几乎立即放弃了 MySQL 控制台。
回答by StaxMan
I had the same problem and the GUI isn't an option for me. Here's how I solved it:
我遇到了同样的问题，GUI 不适合我。这是我解决它的方法：
DELIMITER $$
DROP FUNCTION IF EXISTS sfKillConnection $$
CREATE FUNCTION sfKillConnection() RETURNS INT
BEGIN
SELECT connection_id() into @connectionId;
KILL @connectionId;
RETURN @connectionId;
END $$
DELIMITER ;
It's a function instead of a procedure so it can be called in a script like this:
它是一个函数而不是过程，因此可以在这样的脚本中调用它：
select if(@error, sfKillConnection(), 0);
You'll probably need to start the mysql client with the --disable-reconnectoption.
您可能需要使用该--disable-reconnect选项启动 mysql 客户端。
回答by KCD
MySQL Workbench now replaces MySQL Administrator.
MySQL Workbench 现在取代了 MySQL Administrator。
Use 'Toggle whether execution of SQL script should continue after failed statements'
使用“切换是否在语句失败后继续执行 SQL 脚本”
回答by zombat
Here's a way to execute your upgrade commands only if a certain version is in place.
这是一种仅在特定版本到位时才执行升级命令的方法。
It's ugly but it's all I can suggest for mysql 5.
这很丑陋，但我只能为 mysql 5 提出建议。
DELIMITER $$
DROP FUNCTION IF EXISTS `Upgrade_Build`$$
CREATE FUNCTION `Upgrade_Build`(bversion INT) RETURNS varchar(30)
BEGIN
IF bversion = (SELECT MIN(`db_version`) FROM `system`) THEN
UPDATE `pages`
SET `component_parameters` =
REPLACE(`component_parameters`,'folderviewer_','folder_viewer.');
UPDATE `system`
SET `db_version` = bversion+1;
return CONCAT('Success: ',bversion,' > ',bversion+1);
ELSE
return CONCAT('Failed - Version is ',(SELECT MIN(`db_version`) FROM `system`));
END IF;
END$$
DELIMITER ;
To use it do this:
要使用它，请执行以下操作：
SELECT Upgrade_Build(1327);
You'll see something like this if the current version WAS 1327:
如果当前版本是 1327，您将看到类似的内容：
Success: 1327 > 1328
成功：1327 > 1328
Run it a second time and you'll see:
第二次运行它，你会看到：
Failed - Version is 1328
失败 - 版本为 1328
Hope this is of use to someone.
希望这对某人有用。
回答by Paul Sonier
Looks like KILL QUERYshould do what you need.
看起来KILL QUERY应该做你需要的。
回答by Paul Robinson
For anybody still looking, you can ask MySQL to kill a connection, even the current one:
对于仍在寻找的任何人，您可以要求 MySQL 终止连接，即使是当前的连接：
kill connection_id();
回答by Dave Cherkassky
Here's a low-forehead approach:
这是一种低额头方法：
I needed to make sure that @search_admin_realnamecorresponded to a record in the profilestable; if not, I wanted to abort my script.
我需要确保@search_admin_realname对应于profiles表中的记录；如果没有，我想中止我的脚本。
So I did this:
所以我这样做了：
select @search_admin_id:= userid
from profiles
where realname=@search_admin_realname
;
# test that @search_admin_id is not null;
# if it is, then the insert will fail and script will abort.
create temporary table t_search_admin_id ( search_admin_id mediumint( 9 ) not null );
insert into t_search_admin_id ( search_admin_id ) values ( @search_admin_id );
The only drawback is that I can't really customize the error message :(
唯一的缺点是我无法真正自定义错误消息:(
