Форум пользователей MySQL

FiMko · 03.03.2012 18:15:29

Привет всем! Вы когда-нибудь сталкивались с подобной проблемой (?):

[1] Имеется следующая тестовая таблица:

mysql> describe whitespace_table;

+-------------------+---------+------+-----+---------+-------+

| Field             | Type    | Null | Key | Default | Extra |

+-------------------+---------+------+-----+---------+-------+

| whitespace_column | char(1) | NO   |     | NULL    |       |

+-------------------+---------+------+-----+---------+-------+

В данной таблице хранится лишь одна строка с данными - символ пробела.

[2] Имеется следующая тестовая хранимая процедура:

PROCEDURE proc(

    IN in_string VARCHAR(100)

)

BEGIN

    DECLARE whitespace CHAR(1);

    DECLARE _no_more_rows BOOLEAN DEFAULT FALSE;

    DECLARE _cur CURSOR FOR

    SELECT whitespace_column

    FROM   whitespace_table;

    DECLARE CONTINUE HANDLER FOR NOT FOUND

        SET _no_more_rows = TRUE;

    OPEN _cur;

    the_loop: LOOP

        FETCH  _cur

        INTO   whitespace;

        IF _no_more_rows THEN

            CLOSE _cur;

            LEAVE the_loop;

        END IF;

        SELECT instr(in_string, whitespace);

        SELECT instr(in_string, ' ');

    END LOOP the_loop;

END

После вызова процедуры имеем:

mysql> call proc('123 qwe');

+------------------------------+

| instr(in_string, whitespace) |

+------------------------------+

|                            1 |

+------------------------------+

+-----------------------+

| instr(in_string, ' ') |

+-----------------------+

|                     4 |

+-----------------------+

Собственно, проблема заключается в том, что в первом случае, получив пробел из таблицы в курсоре, функция INSTR считает, что в исходной строке символ пробела присутствует в первой позиции, что неверно. Если же использовать SELECT instr(in_string, ' '), то INSTR отрабатывает верно. Есть какие-либо идеи? Спасибо!

---
Версия MySQL 5.5.13.

Отредактированно FiMko (03.03.2012 20:56:55)

FiMko · 03.03.2012 18:32:12

Из созвучного, найденного в официальной документации:

http://dev.mysql.com/doc/refman/5.0/en/char.html написал:
For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

Я сменил тип колонки на VARCHAR, чтобы проверить возможные предупреждения, но никаких предупреждений об усечении при вставке строки я не получил. Да собственно и почему, я же не превышаю длину поля (trailing spaces in excess of the column length are truncated). Также и в phpMyAdmin в режиме редактирования данных я вижу свой пробел в таблице.

Добавил в хранимую процедуру дополнительные проверки:

SELECT IF(whitespace = '', 1, 0); -- Результат: 1!

SELECT LOCATE(in_string, whitespace, 1); -- Результат 0!

Таким образом, полученное в переменную whitespace из таблицы значение пустое. Не понимаю...

Отредактированно FiMko (03.03.2012 19:13:02)

FiMko · 03.03.2012 18:48:46

Нашел что-то похожее в буржунете:

http://dba.stackexchange.com/questions/6664/mysql-how-do-you-enter-a-record-with-only-whitespace написал:
Whitespace is ignored when doing comparisions. Meaning where col="abc" will match both "abc" and "abc ".

И действительно:

mysql> select IF('qwe ' = 'qwe', 1, 0);

+--------------------------+

| IF('qwe ' = 'qwe', 1, 0) |

+--------------------------+

|                        1 |

+--------------------------+

Но тогда почему правильно отрабатывает SELECT instr(in_string, ' '); ?

Отредактированно FiMko (03.03.2012 18:51:15)

FiMko · 03.03.2012 19:34:56

Прикольно, добился рабочего варианта. Все-таки заработало именно после смены типа для переменной whitespace в процедуре, а также для поля whitespace_column в таблице с CHAR на VARCHAR. Для типа CHAR так и не работало. Подозреваю, это все же из-за этого:

http://dev.mysql.com/doc/refman/5.5/en/char.html написал:
The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated

После этого не работало, пока не указал длину переменной VARCHAR(2) вместо VARCHAR(1). Подозреваю, это из-за того, что используемый Collation - utf8_general_ci (двухбайтовый). Если поменять на Collation, скажем, ascii_general_ci (однобайтовый), то будет работать и для VARCHAR(1).

Итак, конечная рабочая версия процедуры:

PROCEDURE lexikos_inno.proc(

    IN in_string VARCHAR(100)

)

BEGIN

    DECLARE whitespace VARCHAR(2); -- for utf8_general_ci two bytes

    DECLARE _no_more_rows BOOLEAN DEFAULT FALSE;

    DECLARE _cur CURSOR FOR

    SELECT whitespace_column

  FROM   whitespace_table;

    DECLARE CONTINUE HANDLER FOR NOT FOUND

        SET _no_more_rows = TRUE;

    OPEN _cur;

    the_loop: LOOP

        FETCH  _cur

        INTO   whitespace;

        IF _no_more_rows THEN

            CLOSE _cur;

            LEAVE the_loop;

        END IF;

        SELECT instr(in_string, whitespace);

    END LOOP the_loop;

END

Отредактированно FiMko (03.03.2012 20:54:39)

vasya · 04.03.2012 00:42:30

Люблю такие темы
Всё подробно расписано и отвечать не надо. Почитал и сделал заметку в памяти о такой особенности.

Форум пользователей MySQL

#1 03.03.2012 18:15:29

Неправильная работа INSTR с символом пробела в курсоре?

#2 03.03.2012 18:32:12

Re: Неправильная работа INSTR с символом пробела в курсоре?

http://dev.mysql.com/doc/refman/5.0/en/char.html написал:

#3 03.03.2012 18:48:46

Re: Неправильная работа INSTR с символом пробела в курсоре?

http://dba.stackexchange.com/questions/6664/mysql-how-do-you-enter-a-record-with-only-whitespace написал:

#4 03.03.2012 19:34:56

Re: Неправильная работа INSTR с символом пробела в курсоре?

http://dev.mysql.com/doc/refman/5.5/en/char.html написал:

#5 04.03.2012 00:42:30

Re: Неправильная работа INSTR с символом пробела в курсоре?

Board footer