如何在mysql中找到运行NULLIF()或为空的最后一行? [英] How to find last row that ran NULLIF() or has null in mysql?

问题描述

我目前运行此操作将空字符串转换为 null.有什么方法可以找出经过 NULLIF() 操作或具有空字符的最后一行，以便我可以从那一刻开始处理所有内容.我的表有一个时间戳列.我有 150 个像 recovery_email 这样的列，我想从在其中任何一个中找到的最后一个空字符串开始.

I currently run this operation to convert empty strings to null. Is any way to find out the last row that has gone through NULLIF() operation or has null character so I can process everything from that point. My table has a timestamp column. I have 150 columns like recovery_email and I'd like to start from the last empty string was found in either of them.

UPDATE table                                                                                                 
    SET recovery_email = NULLIF(recovery_email, ''), # There are 150 columns like recovery_email.
    email = NULLIF(email, ''),
    WHERE timestamp >= (NOW() - INTERVAL 1 DAY)   

推荐答案

这回答了问题的原始版本.

This answers the original version of the question.

为什么要使用 NULLIF()?只需过滤到您想要的行:

Why would you use NULLIF()? Just filter down to the rows you want:

UPDATE table                                                                                                 
    SET recovery_email = NULL                                
    WHERE timestamp >= (NOW() - INTERVAL 1 DAY) AND recovery_email = '';

您可以在 recovery_email 上建立索引，而不必担心尝试按时间过滤.

You can put an index on recovery_email and not worry about attempting to filter by time.

或者，更好的是，使用 check 约束(在最新版本的 MySQL 中)定义列，因此不允许空字符串.

Or, better yet, defined the column with a check constraint (in the most recent versions of MySQL) so empty strings are not allowed.

这篇关于如何在mysql中找到运行NULLIF()或为空的最后一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！