MySQL：DECLARE CONTINUE HANDLER 在出现错误时继续，打印警告
我正在审查/重新设计/重构数据库，并希望创建一个新数据库，以更智能的方式存储几乎相同的数据。 “遗留”数据库的问题之一是它没有正确使用键和索引，因此在不应该有的地方出现了重复的条目。
我编写了一个 python 脚本，它从旧数据库中读取数据并生成一个 SQL 脚本，该脚本又使用许多非常相似的 SQL INSERT 语句将这些值从旧数据库插入到新数据库中。 SQL 脚本每次遇到带有错误消息的重复条目时都会停止
第 3086 行出现错误 1062 (23000)：键“lat_lon_height”重复输入“56.450000000--3.366670000-121”
AFAICT 正是它应该做的。然而，目前，我只希望脚本继续运行，不插入重复的条目，而是打印有关它们的警告。我尝试按照以下几种方式在脚本开头安装继续处理程序 MySQL 文档 和一些其他在线资源，但所有这些都只会产生语法错误：
DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Duplicate key in unique index';
或
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
SELECT 'Duplicate key in unique index';
或
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
BEGIN
SELECT 'Duplicate key in unique index';
END;
我做错了什么？
I am reviewing / redesigning / refactoring a database and want to create a new database that stores pretty much the same data in a smarter fashion. One of the problems in the 'legacy' database is that it does not make proper use of keys and indices, so there is duplicate entries where there should be none.
I have written a python script that reads data from the legacy database and generates a SQL script which in turn inserts these values from the legacy database into the new database using a lot of very similar SQL INSERT statements. The SQL script stops every time it encounters a duplicate entry with an error message
ERROR 1062 (23000) at line 3086: Duplicate entry '56.450000000--3.366670000-121' for key 'lat_lon_height'
which AFAICT is exactly what it should do. However, for the moment, I just want the script to keep going, not insert the duplicate entries, but print a warning about them. I tried installing a continue handler at the beginning of the script in several ways following the MySQL docs and some other online resources, but all of them just create a syntax error:
DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Duplicate key in unique index';
or
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
SELECT 'Duplicate key in unique index';
or
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
BEGIN
SELECT 'Duplicate key in unique index';
END;
What am I doing wrong ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除了在存储过程内部之外，我没有使用过处理程序，在存储过程中我对它们的使用有限但很顺利。也许您可以将代码制作成存储过程，然后运行该存储过程？您可能会发现 MySQL 接受您正在尝试执行的操作。
I haven't used handlers except inside of stored procedures, where my use of them has been limited but uneventful. Perhaps you can make your code into a stored procedure and then run the stored procedure? You might find MySQL accepts what you are trying to do then.
好的，让 SQL 脚本简单地打印发生的所有错误的一种方法是使用 --force 命令行选项，如
仍然不知道如何处理重复条目，但是一旦我弄清楚了（不是 SQL 问题），这可能会有所帮助。
不过，很想让继续处理程序工作......
OK, one way to make the SQL script simply print all the errors that occur is to use the --force command line option as in
Still don't know what to actually do about the duplicate entries, but once I've figured that out (not a SQL problem), this is probably going to help.
Would love to get the continue handler to work, though...