mysql - 如何删除包含非数字字段的 MySQL 记录？

Question

由于数据损坏，我们的数据库字段 (Mysql 5) 已填充文本。它是 ExpressionEngine CMS 中的自定义字段，并未仅设置为数字。我只需要删除该字段填充不正确的那些记录。它包含 URL 而不是 ID，例如10937。

我需要运行一个查询/存储过程，它只会查找那些已错误填充的记录，即不包含数字 ID 的记录，并删除这些记录以及另一个表中的链接记录。

我欢迎有关最佳方法的建议。我可以通过 PHP 完成此操作，但希望在存储过程中完成此操作，因为这将是一项有用的学习技能。 This question包含可以测试字段是否为数字的函数的详细信息。但我实际上需要相反的东西。

我将选择相关记录的查询是:

SELECT field_id_58 as 'release_id' , ewt.entry_id FROM exp_weblog_data ewd
LEFT JOIN exp_weblog_titles ewt ON ewt.`entry_id` = ewd.`entry_id`
WHERE ewt.weblog_id = 15

这将返回我正在处理的所有 cms entry_ids，包括我想要删除的那些。数据不正确的字段是field_id_58。该字段填充错误的事实意味着我们现在表 ewt 和 ewd 中有重复的记录。

这是我到目前为止所拥有的:

DELIMITER //

CREATE PROCEDURE `proc_DEL_DUPLICATE_PR`
BEGIN
    DECLARE empty INT;
    DECLARE result ??? # not sure what data type this should be
    DECLARE cur1 CURSOR FOR SELECT field_id_58 as 'release_id' , ewt.entry_id FROM exp_weblog_data ewd
                            LEFT OUTER JOIN exp_weblog_titles ewt ON ewt.`entry_id` = ewd.`entry_id`
                            WHERE ewt.weblog_id = 15;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET empty=1;
    OPEN cur1;
    SET empty =0;
    WHILE empty = 0 DO
        FETCH cur1 INTO result;
        #if the release_id in the result row contains a URL string then delete rows for tables ewt, ewd for that entry_id
        END IF;
    END WHILE;

    CLOSE cur1;

END //

Answer 1

您可以使用正则表达式来查找非数字字段。以下查询将查找指定列的值为非数字的所有行:

SELECT * FROM table_name WHERE column_name REGEXP '[^0-9].+'

不会返回任何具有纯数值的行。如果您的DELETE语句使用相同的WHERE子句，那么应该可以做到。

要删除包含非数字列值的行，请尝试以下查询:

DELETE FROM exp_weblog_titles ewt 
    INNER JOIN exp_weblog_data ewd ON
        ewt.`entry_id` = ewd.`entry_id` 
    WHERE ewt.weblog_id = 15 AND ewd.field_id_58 REGEXP '[^0-9].+';

DELETE FROM exp_weblog_data ewd WHERE ewd.field_id_58 REGEXP '[^0-9].+';

它首先从子表中删除所有相关行，然后删除父表行。显然，您需要自行承担使用风险。