loading..
Русский    English
23:35
листать

Функция LEN(), концевые пробелы и уникальность

Недавно я столкнулся с тем, что не смог добавить два значения типа VARCHAR, отличающиеся только концевым пробелом в столбец составного первичного ключа (SQL Server 2008). Возможно, этот факт для кого-то является очевидным, но мне показалось странным, что в принципе разные значения считаются дубликатами. Со значениями типа CHAR(n), который имеет фиксированную длину, все понятно, т.к. короткие строки дополняются пробелами до длины n. Поэтому вводимые значения, которые отличаются лишь концевыми пробелами, оказываются неразличимыми. Но концевой пробел в значении типа VARCHAR является как бы обычным символом.

Вот простой эксперимент.

  1. CREATE TABLE Test_Trailing_Space2
  2.  ( num int NOT NULL, name VARCHAR(10) NOT NULL,
  3.  PRIMARY KEY(num, name) );
  4. GO
  5. INSERT INTO Test_Trailing_Space2 VALUES(1, 'John');
  6. INSERT INTO Test_Trailing_Space2 VALUES(1, 'John ');
  7. GO

Вторая строка не будет вставлена в таблицу, при этом будет получено сообщение о нарушении ограничения первичного ключа. Т.е. вторая строка считается дубликатом первой. Может быть дело в том, что при вставке концевой пробел был отсечен. Но нет, вставим другую уникальную строку с концевым пробелом и проверим наличие в ней концевого пробела:

  1. INSERT INTO Test_Trailing_Space2 VALUES(2, 'John ');
  2. GO
  3. SELECT *, LEN(name) len1,DATALENGTH(name) len2
  4. FROM Test_Trailing_Space2;

Результат:

num name len1 len2
1 John 4 4
2 John 4 5

Значение в столбце len2 показывает, что пробел присутствует в данных, но, тем не менее, значения 'John' и 'John ' считаются дубликатами:

  1. SELECT DISTINCT name
  2. FROM Test_Trailing_Space2;

name
John

Очевидно, что все дело в функции LEN(), которая, как известно, не учитывает концевые пробелы. Я не нашел этой информации в BOL, но, видимо, именно эта функция используется при сравнении значений любых строковых типов. Мне стало интересно, как ведут себя другие СУБД в этом случае, и я повторил эксперимент для MySQL и PostgreSQL. Были получены следующие результаты.

MySQL (версия 5.0)

  1. SELECT *, OCTET_LENGTH(name) AS len1, LENGTH(name) AS len2
  2. FROM Test_Trailing_Space2;

1 John 4 4
2 John 5 5


 
  1. SELECT DISTINCT name FROM Test_Trailing_Space2;

John

PostgreSQL (версия 8.3.6)

  1. SELECT *, OCTET_LENGTH(name) AS len1, LENGTH(name) AS len2
  2. FROM Test_Trailing_Space2;

1 "John" 4 4
2 "John " 5 5

  1. SELECT DISTINCT name
  2. FROM Test_Trailing_Space2;

"John"
"John "

Как видно, и MySQL, и PostgreSQL учитывают пробел как в числе символов, так и в числе байтов, используемых для хранения строкового значения. При этом MySQL и SQL Server, в отличие от PostgreSQL, считают строки, различающиеся лишь концевыми пробелами, дубликатами. Естественно, PostgreSQL позволяет вставить и такую строку в рассматриваемую таблицу:

  1.  INSERT INTO Test_Trailing_Space2 VALUES(1, 'John ');

Вместо выводов

Я далек от того, чтобы высказываться относительно правильности той или иной реализации и, тем более, спорить о том, какая СУБД лучше. Я считаю, что нужно знать досконально ту СУБД, которую вы используете в своей профессиональной деятельности. Изучайте документацию и все подвергайте проверке, не полагаясь на интуицию и «здравый» смысл.

Рекомендуемые упражнения: 131

Bookmark and Share
LEN
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100