比较时需要考虑varchar中的尾随空白 [英] Trailing whitespace in varchar needs to be considered in comparison

问题描述

我正在使用带有varchar列的表.我没有意识到比较中没有考虑尾随空格(显然，只有尾随空格数量不同的两个值会违反唯一性属性(如果指定).

I am using a table with a varchar column. I did not realize that trailing whitespace is not considered in comparisons (and that, apparently, two values that differ only in amount of trailing whitespace will violate the uniqueness property, if specified).

我需要将其固定在桌子上，最好是固定在桌子上.在MySQL中是否有建议的方法来修复这样的表?

I need to fix this in the table, preferably in place. Is there a recommended path to fixing a table like this in MySQL?

我严格通过我控制的程序访问数据库，因此切换到非人类可读格式(如二进制)就可以了.但是我不确定该怎么做，也不想破坏桌子.

I am accessing the DB strictly through a program I control, so switching to a non-human readable format such as binary would be fine. But I am not sure how to do such a thing and don't want to destroy the table.

推荐答案

我必须假设您使用的是MySQL 5.x，因为MySQL 4.x不在VARCHAR列中存储尾随空格.

I have to assume you're using MySQL 5.x because MySQL 4.x doesn't store trailing spaces in a VARCHAR column.

正如您所指出的那样，在MySQL中使用标准的=运算符，不考虑尾随空格:

Using the standard = operator in MySQL, as you indicated, trailing spaces are not considered:

SELECT 'this' = 'this '返回TRUE

但是， LIKE 逐个字符地比较字符串，因此尾随空格很重要.

However, LIKE compares the strings character by character, so trailing spaces are significant.

SELECT 'this' LIKE 'this '返回FALSE.

使用默认排序规则，=和LIKE可能都不区分大小写.如果需要比较它们，请使用 COLLATE 子句指定排序规则以区分大小写的方式.

Both = and LIKE may be case insensitive, using the default collation. Use the COLLATE clause to specify the collation if you need to compare them in a case sensitive manner.

这篇关于比较时需要考虑varchar中的尾随空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！