Поиск

Проблемы, связанные с запросами

Чувствительность к регистру во время поиска

По умолчанию во время выполнения операций поиска в MySQL регистр не учитывается (хотя существуют определенные наборы символов, которые нечувствительными к регистру быть не могут, как, например, набор czech ). Это означает, что во время поиска с помощью имя_столбца LIKE ' a%', будут представлены все значения столбца, которые начинаются с А или а. Если нужно, чтобы при таком поиске регистр учитывался, следует проследить, чтобы один из операндов являлся бинарной строкой. Также добиться подобного результата можно и с помощью оператора BINARY . Запишите условие либо как BINARY имя_столбца LIKE 'а%', либо как имя_столбца LIKE BINARY f a %'.

При необходимости, чтобы во время поиска имени столбца регистр учитывался всегда, объявите столбец как binary .

Простые операции сравнения (>=, >, =, <, <=, сортировка и группирование) основаны на значении сортировки каждого символа. Символы с одинаковым значением сортировки (такие как СЕ', 'е' и 'ё') трактуются как один и тот же символ.

Если используются данные на китайском языке в так называемой кодировке big 5, все символьные столбцы могут быть объявлены как BINARY . Это сработает, поскольку порядок сортировки символов кодировки big 5 основывается на порядке кодов ASCII . Начиная с версии MySQL 4.1, объявить, что для столбца всегда должен использоваться набор символов big 5, можно следующим образом:

CREATE TABLE t (name CHAR(40) CHARACTER SET big5);

Проблемы при использовании столбцов DATE

Для значения DATE используется формат ' ГГГГ-ММ-ДД'. Согласно SQL -стандарту, любой другой формат является недопустимым. Данный формат должен применяться в выражениях UPDATE и в конструкциях WHERE операторов SELECT , например:

mysql> SELECT * FROM имя _ таблицы WHERE date >= '2003-05-05';

Для удобства MySQL автоматически преобразует дату в число, если дата используется в числовом контексте (и наоборот), а также допускает использование ослабленной строковой формы при обновлении и в конструкции WHERE , сравнивающей дату со столбцом TIMESTAMP , DATE или DATETIME . (Ослабленная форма означает, что в качестве разделителя между частями может применяться любой знак пунктуации. Например, * 2004- 08-15' и '2004#08#15' эквивалентны.) MySQL может также преобразовывать строку, не содержащую разделителей (как, например, '20040815'), при условии что она имеет смысл в качестве даты.

Специальную дату '0000-00-00' можно сохранять и извлекать в виде '0000-00-00'. При использовании даты типа '0000-00-00' через Connector / ODBC , в версии Connec tor / ODBC 2.50.12 и выше она будет автоматически преобразовываться в NULL , поскольку ODBC не может обрабатывать такой формат даты.

Поскольку MySQL выполняет описанные выше типы преобразований, работать будут следующие операторы:

mysql> INSERT INTO имя_ таблицы (idate) VALUES (19970505) ;

mysql> INSERT INTO имя_таблицы (idate) VALUES ('19970505');

mysql> INSERT INTO имя_таблицы (idate) VALUES ('97-05-05');

mysql> INSERT INTO имя_таблицы (idate) VALUES ('1997.05.05');

mysql> INSERT INTO имя_таблицы (idate) VALUES ('1997 05 05');

mysql> INSERT INTO имя_таблицы (idate) VALUES ('0000-00-00');

mysql> SELECT idate FROM имя_таблицы WHERE idate >= '1997-05-05';

mysql> SELECT idate FROM имя_таблицы WHERE idate >= 19970505;

mysql> SELECT MOD(idate,100) FROM имя_таблицы WHERE idate >= 19970505;

mysql> SELECT idate FROM имя_таблицы WHERE idate >= '19970505';

Однако такой оператор работать не будет:

mysql> SELECT idate FROM имя_таблицы WHERE STRCMP(idate,'20030505')=0;

Функция STRCMP () является строковой, поэтому она преобразует idate в строку формата 'ГГГГ-ММ-ДД' и выполняет операцию по сравнению строк. Преобразовывать ' 20030505' в дату и сравнивать даты она не будет.

Сервер MySQL упаковывает даты для хранения, поэтому сохранить определенную дату, если она для буфера результатов не подходит, он не может. MySQL выполняет очень ограниченную проверку того, является ли та или иная дата правильной. При сохранении некорректно указанной даты, например, '2004-2-31', MySQL сохранит ее именно в таком виде. Поддерживаются следующие правила принятия дат:

я Если MySQL может сохранять и извлекать дату в заданном формате, она принимается для столбцов DATE и DATETIME даже в случае, когда такой ее формат не является стопроцентно корректным.

  • Значения дней в интервале от 0 до 31 принимаются для любой даты. Это оченьудобно для Web -приложений, в которых год, месяц и день указываются в трех разных полях.
  • Значение дня или месяца может быть равно нулю. Это удобно, когда, например, нужно сохранить дату рождения в столбце DATE , а она не известна полностью.

Если дата не может быть преобразована в какое-нибудь подходящее значение, в столбце DATE сохраняется значение 0, и при извлечении дата будет выглядеть как '0000- 00-00'. Делается это для удобства, а также из соображений, связанных с производитель&ностью. Мы полагаем, что сервер баз данных должен извлекать дату в сохраненном вами виде (даже если эта дата не была логически корректной во всех случаях), а также мы считаем, что приложение, а не сервер отвечает за проверку дат.

Проблемы со значениями NULL

Понятие значения NULL является наиболее распространенным источником заблуждения для новичков в SQL , которые часто думают, что NULL - это то же самое, что и пустая строка , Это совершенно не верно. Например , следующие операторы абсолютно разные :

mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES ( );

Оба оператора вставляют значение в столбец phone , но первый из них вставляет значение NULL , а второй - пустую строку. Смысл первого можно рассматривать как номер телефона не известен, а смысл второго - как известно, что у этого человека нет телефона, а, следовательно, у него нет и номера телефона.

С обработкой NULL помочь могут операции IS NULL и NOT NULL , а также функция

IFNULLO .

В SQL значение NULL никогда не бывает истинным при сравнении с любым другим значением и даже со значением NULL . Выражение, которое содержит NULL , всегда выдает значение NULL , если только в документации по операциям и функциям, задействованным в таком выражении, не было указано какое-нибудь другое значение. В следующем примере все столбцы возвращают NULL :

mysql > SELECT NULL , 1+ NULL , CONCAT (' Invisible 1 , NULL );

Для поиска в столбцах значений NULL использовать критерий выражение - NULL нельзя. Следующий оператор не возвращает никаких строк, поскольку выражение = null никогда не бывает истинным для любого выражения.

mysql> SELECT * FROM my_table WHERE phone = NULL;

Для поиска значений NULL необходимо использовать конструкцию IS NULL . Следующие операторы демонстрируют, как найти телефонный номер NULL или пустой телефонный номер:

mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = '';

Добавлять индекс для столбца, наличие значений NULL в котором не исключено, можно при условии, что используется версия MySQL 3.23.2 или выше, а также механизм хранения MyISAM , innoDB или BDB . Начиная с MySQL 4.0.2, механизмом хранения MEMORY также поддерживаются значения NULL в индексах. Во всех остальных случаях потребуется объявлять индексированные таблицы с помощью NOT NULL , а также вставить NULL в столбец будет невозможно.

При считывании данных с помощью LOAD DATA INFILE пустые или недостающие столбцы обновляются со значениями • •. Если нужно в столбец занести значение NULL , используйте N в файле данных. В определенных обстоятельствах также может использоваться и литеральное слово NULL .

При применении DISTINCT , GROUP BY или ORDER BY все значения NULL рассматриваются как равные.

Когда используется ORDER BY , значения NULL предоставляются первыми или же последними, если указать DESC для сортировки в порядке убывания. Исключение: в MySQL 4.0.2 (как и в версии 4.0.10) значения NULL сортируются первыми, независимо от порядка сортировки.

Агрегатные (итоговые) функции, такие как COUNT (), MIN() и SUMO , игнорируют все значения NULL . Исключение составляет функция COUNT (*), которая подсчитывает строки, а не отдельные значения в столбцах. Например, следующий оператор выполняет два типа подсчета. Сначала подсчитывается число строк в таблице, а затем - количество неравных NULL значений в столбце age :

mysql> SELECT COUNT (*), COUNT(age) FROM person;

Для некоторых типов столбцов MySQL обрабатывает значения NULL специальным образом. При вставке null в столбец timestamp в него добавляются значения текущей даты и времени. При вставке NULL в столбец целых чисел с атрибутом AUTO _ INCREMENT вставлено будет следующее число в последовательности.

Проблемы с псевдонимами столбцов

В конструкциях GROUP BY, ORDER by или HAVING псевдоним можно использовать для ссылки на столбец; также псевдонимы могут применяться с целью дать тому или иному столбцу более подходящее название:

SELECT SQRT(a*b) AS route FROM имя _ таблицы GROUP BY route HAVING route > 0; SELECT id, COUNT(*) AS cnt FROM имя_таблицы GROUP BY id HAVING cnt > 0; SELECT id AS 'Customer identity' FROM имя _ таблицы ;

Стандарт SQL запрещает ссылаться на псевдоним столбца в конструкции WHERE . Причина этого заключается в том, что во время выполнения кода WHERE значение столбца может оказаться еще не определенным. Например, следующий запрос является недо&пустимым:

SELECT id , COUNT (*) AS cnt FROM имя_ таблицы WHERE cnt > 0 GROUP BY id ;

Конструкция WHERE выполняется для определения, какие строки должны быть включены в часть GROUP BY , в то время как HAVING применяется для определения, какие строки из набора результатов должны быть использованы.

Сбой оператора ROLLBACK при работе с нетранзакционными таблицами

Получение следующего сообщения при попытке выполнить ROLLBACK означает, что одна или более таблиц, которые использовались в транзакции, транзакций не поддерживают.

Warning: Some non-transactional changed tables couldn't be rolled back

На такие нетранзакционные таблицы оператор ROLLBACK не действует.

Если намеренного смешения транзакционных и нетранзакционных таблиц при вы&полнении транзакции не было, причина выдачи подобного сообщения, скорее всего, будет заключаться в том, что таблица, рассматриваемая как транзакционная, на самом деле таковой не является. Такое может случиться при попытке создать таблицу с использованием транзакционного механизма хранения, который не поддерживается сервером mysqld (или был отключен с помощью опции запуска). Если mysqld не поддерживает механизм хранения, он вместо этого создает таблицу как таблицу MyISAM , которая тран- закционной не является.

Проверить тип той или иной таблицы можно с помощью следующих операторов:

SHOW TABLE STATUS LIKE ' имя_таблицы х; SHOW CREATE TABLE имя_таблицы;

Проверить поддерживаемые сервером mysqld механизмы хранения можно с помощью следующего оператора: SHOW ENGINES ;

В версиях MySQL , предшествующих 4.1.2, оператор SHOW ENGINES недоступен. Используйте вместо него следующий оператор и проверяйте значение переменной, отвечающей за интересующий вас механизм хранения:

SHOW VARIABLES LIKE 'have_%';

Например, для определения, поддерживается ли innoDB , следует проверить значение переменной have _ innodb .

Чувствительность к регистру во время поиска

По умолчанию во время выполнения операций поиска в MySQL регистр не учитывается (хотя существуют определенные наборы символов, которые нечувствительными к регистру быть не могут, как, например, набор czech ). Это означает, что во время поиска с помощью имя_столбца LIKE ' a%', будут представлены все значения столбца, которые начинаются с А или а. Если нужно, чтобы при таком поиске регистр учитывался, следует проследить, чтобы один из операндов являлся бинарной строкой. Также добиться подобного результата можно и с помощью оператора BINARY . Запишите условие либо как BINARY имя_столбца LIKE 'а%', либо как имя_столбца LIKE BINARY f a %'.

При необходимости, чтобы во время поиска имени столбца регистр учитывался всегда, объявите столбец как binary

Простые операции сравнения (>=, >, =, <, <=, сортировка и группирование) основаны на значении сортировки каждого символа. Символы с одинаковым значением сортировки (такие как СЕ', 'е' и 'ё') трактуются как один и тот же символ.

Если используются данные на китайском языке в так называемой кодировке big 5, все символьные столбцы могут быть объявлены как BINARY . Это сработает, поскольку порядок сортировки символов кодировки big 5 основывается на порядке кодов ASCII . Начиная с версии MySQL 4.1, объявить, что для столбца всегда должен использоваться набор символов big 5, можно следующим образом:

CREATE TABLE t (name CHAR(40) CHARACTER SET big5);

Проблемы при использовании столбцов DATE

Для значения DATE используется формат ' ГГГГ-ММ-ДД'. Согласно SQL -стандарту, любой другой формат является недопустимым. Данный формат должен применяться в выражениях UPDATE и в конструкциях WHERE операторов SELECT , например:

mysql> SELECT * FROM имя _ таблицы WHERE date >= '2003-05-05';

Для удобства MySQL автоматически преобразует дату в число, если дата используется в числовом контексте (и наоборот), а также допускает использование ослабленной строковой формы при обновлении и в конструкции WHERE , сравнивающей дату со столбцом TIMESTAMP , DATE или DATETIME . (Ослабленная форма означает, что в качестве разделителя между частями может применяться любой знак пунктуации. Например, * 2004- 08-15' и '2004#08#15' эквивалентны.) MySQL может также преобразовывать строку, не содержащую разделителей (как, например, '20040815'), при условии что она имеет смысл в качестве даты.

Специальную дату '0000-00-00' можно сохранять и извлекать в виде '0000-00-00'. При использовании даты типа '0000-00-00' через Connector / ODBC , в версии Connec tor / ODBC 2.50.12 и выше она будет автоматически преобразовываться в NULL , поскольку ODBC не может обрабатывать такой формат даты.

Поскольку MySQL выполняет описанные выше типы преобразований, работать будут следующие операторы:

mysql> INSERT INTO имя_ таблицы (idate) VALUES (19970505) ;

mysql> INSERT INTO имя_таблицы (idate) VALUES ('19970505');

mysql> INSERT INTO имя_таблицы (idate) VALUES ('97-05-05');

mysql> INSERT INTO имя_таблицы (idate) VALUES ('1997.05.05');

mysql> INSERT INTO имя_таблицы (idate) VALUES ('1997 05 05');

mysql> INSERT INTO имя_таблицы (idate) VALUES ('0000-00-00');

mysql> SELECT idate FROM имя_таблицы WHERE idate >= '1997-05-05';

mysql> SELECT idate FROM имя_таблицы WHERE idate >= 19970505;

mysql> SELECT MOD(idate,100) FROM имя_таблицы WHERE idate >= 19970505;

mysql> SELECT idate FROM имя_таблицы WHERE idate >= '19970505';

Однако такой оператор работать не будет:

mysql> SELECT idate FROM имя_таблицы WHERE STRCMP(idate,'20030505')=0;

Функция STRCMP () является строковой, поэтому она преобразует idate в строку формата 'ГГГГ-ММ-ДД' и выполняет операцию по сравнению строк. Преобразовывать ' 20030505' в дату и сравнивать даты она не будет.

Сервер MySQL упаковывает даты для хранения, поэтому сохранить определенную дату, если она для буфера результатов не подходит, он не может. MySQL выполняет очень ограниченную проверку того, является ли та или иная дата правильной. При сохранении некорректно указанной даты, например, '2004-2-31', MySQL сохранит ее именно в таком виде. Поддерживаются следующие правила принятия дат:

я Если MySQL может сохранять и извлекать дату в заданном формате, она принимается для столбцов DATE и DATETIME даже в случае, когда такой ее формат не является стопроцентно корректным.

  • Значения дней в интервале от 0 до 31 принимаются для любой даты. Это очень удобно для Web -приложений, в которых год, месяц и день указываются в трех разных полях.
  • Значение дня или месяца может быть равно нулю. Это удобно, когда, например, нужно сохранить дату рождения в столбце DATE , а она не известна полностью.

Если дата не может быть преобразована в какое-нибудь подходящее значение, в столбце DATE сохраняется значение 0, и при извлечении дата будет выглядеть как '0000- 00-00'. Делается это для удобства, а также из соображений, связанных с производитель&ностью. Мы полагаем, что сервер баз данных должен извлекать дату в сохраненном вами виде (даже если эта дата не была логически корректной во всех случаях), а также мы считаем, что приложение, а не сервер отвечает за проверку дат.

Проблемы со значениями NULL

Понятие значения NULL является наиболее распространенным источником заблуждения для новичков в SQL , которые часто думают, что NULL - это то же самое, что и пустая строка , Это совершенно не верно. Например , следующие операторы абсолютно разные :

mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES ( );

Оба оператора вставляют значение в столбец phone , но первый из них вставляет значение NULL , а второй - пустую строку. Смысл первого можно рассматривать как номер телефона не известен, а смысл второго - как известно, что у этого человека нет телефона, а, следовательно, у него нет и номера телефона.

С обработкой NULL помочь могут операции IS NULL и NOT NULL , а также функция

IFNULLO .

В SQL значение NULL никогда не бывает истинным при сравнении с любым другим значением и даже со значением NULL . Выражение, которое содержит NULL , всегда выдает значение NULL , если только в документации по операциям и функциям, задействованным в таком выражении, не было указано какое-нибудь другое значение. В следующем примере все столбцы возвращают NULL :

mysql > SELECT NULL , 1+ NULL , CONCAT (' Invisible 1 , NULL );

Для поиска в столбцах значений NULL использовать критерий выражение - NULL нельзя. Следующий оператор не возвращает никаких строк, поскольку выражение = null никогда не бывает истинным для любого выражения.

mysql> SELECT * FROM my_table WHERE phone = NULL;

Для поиска значений NULL необходимо использовать конструкцию IS NULL . Следующие операторы демонстрируют, как найти телефонный номер NULL или пустой телефонный номер:

mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = '';

Добавлять индекс для столбца, наличие значений NULL в котором не исключено, можно при условии, что используется версия MySQL 3.23.2 или выше, а также механизм хранения MyISAM , innoDB или BDB . Начиная с MySQL 4.0.2, механизмом хранения MEMORY также поддерживаются значения NULL в индексах. Во всех остальных случаях потребуется объявлять индексированные таблицы с помощью NOT NULL , а также вставить NULL в столбец будет невозможно.

При считывании данных с помощью LOAD DATA INFILE пустые или недостающие столбцы обновляются со значениями Если нужно в столбец занести значение NULL , используйте N в файле данных. В определенных обстоятельствах также может использоваться и литеральное слово NULL .

При применении DISTINCT , GROUP BY или ORDER BY все значения NULL рассматриваются как равные.

Когда используется ORDER BY , значения NULL предоставляются первыми или же последними, если указать DESC для сортировки в порядке убывания. Исключение: в MySQL 4.0.2 (как и в версии 4.0.10) значения NULL сортируются первыми, независимо от порядка сортировки.

Агрегатные (итоговые) функции, такие как COUNT (), MIN() и SUMO , игнорируют все значения NULL . Исключение составляет функция COUNT (*), которая подсчитывает строки, а не отдельные значения в столбцах. Например, следующий оператор выполняет два типа подсчета. Сначала подсчитывается число строк в таблице, а затем - количество неравных NULL значений в столбце age :

mysql> SELECT COUNT (*), COUNT(age) FROM person;

Для некоторых типов столбцов MySQL обрабатывает значения NULL специальным образом. При вставке null в столбец timestamp в него добавляются значения текущей даты и времени. При вставке NULL в столбец целых чисел с атрибутом AUTO _ INCREMENT вставлено будет следующее число в последовательности.

Проблемы с псевдонимами столбцов

В конструкциях GROUP BY, ORDER by или HAVING псевдоним можно использовать для ссылки на столбец; также псевдонимы могут применяться с целью дать тому или иному столбцу более подходящее название:

SELECT SQRT(a*b) AS route FROM имя _ таблицы GROUP BY route HAVING route > 0; SELECT id, COUNT(*) AS cnt FROM имя_таблицы GROUP BY id HAVING cnt > 0; SELECT id AS 'Customer identity' FROM имя _ таблицы ;

Стандарт SQL запрещает ссылаться на псевдоним столбца в конструкции WHERE . Причина этого заключается в том, что во время выполнения кода WHERE значение столбца может оказаться еще не определенным. Например, следующий запрос является недо&пустимым:

SELECT id , COUNT (*) AS cnt FROM имя_ таблицы WHERE cnt > 0 GROUP BY id ;

Конструкция WHERE выполняется для определения, какие строки должны быть включены в часть GROUP BY , в то время как HAVING применяется для определения, какие строки из набора результатов должны быть использованы.

Сбой оператора ROLLBACK при работе с нетранзакционными таблицами

Получение следующего сообщения при попытке выполнить ROLLBACK означает, что одна или более таблиц, которые использовались в транзакции, транзакций не поддерживают.

Warning: Some non-transactional changed tables couldn't be rolled back

На такие нетранзакционные таблицы оператор ROLLBACK не действует.

Если намеренного смешения транзакционных и нетранзакционных таблиц при вы&полнении транзакции не было, причина выдачи подобного сообщения, скорее всего, будет заключаться в том, что таблица, рассматриваемая как транзакционная, на самом деле таковой не является. Такое может случиться при попытке создать таблицу с использованием транзакционного механизма хранения, который не поддерживается сервером mysqld (или был отключен с помощью опции запуска). Если mysqld не поддерживает механизм хранения, он вместо этого создает таблицу как таблицу MyISAM , которая тран- закционной не является.

Проверить тип той или иной таблицы можно с помощью следующих операторов:

SHOW TABLE STATUS LIKE ' имя_таблицы х; SHOW CREATE TABLE имя_таблицы;

Проверить поддерживаемые сервером mysqld механизмы хранения можно с помощью следующего оператора: SHOW ENGINES ;

В версиях MySQL , предшествующих 4.1.2, оператор SHOW ENGINES недоступен. Используйте вместо него следующий оператор и проверяйте значение переменной, отвечающей за интересующий вас механизм хранения:

SHOW VARIABLES LIKE 'have_%';

Например, для определения, поддерживается ли innoDB , следует проверить значение переменной have _ innodb .

Удаление строк из связанных таблиц

В версиях MySQL , предшествующих 4.1, не поддерживаются подзапросы, а в верси­ ях ниже 4.0 не поддерживается использование более чем одной таблицы в операторе delete . Если установлена именно одна из упомянутых версий, удалить строки их двух связанных между собой таблиц можно с помощью следующего подхода:

  • В главной таблице выберите строки с помощью SELECT , основываясь на некотором условии WHERE .
  • Удалите строки с помощью DELETE из главной таблицы, основываясь на том же самом условии.
  • Удалите строки с помощью DELETE FROM связанная_ та блица WHERE связанный_столбец IN {выбраные_ строки).

Если общая длина оператора для таблицы связанная_ та блица превышает 1 Мбайт (значение системной переменной max _ allowed _ packet по умолчанию), необходимо раз­ делить его на части поменьше и выполнить сразу несколько операторов delete . Скорее всего, самый быстрый оператор DELETE получится при указании только от 100 до 1000 значений связанный_столбец в каждом таком операторе, если связанный_ с толбец про­индексирован. Если столбец связанный_ столбец не проиндексирован, скорость выполнения не будет зависеть от количества аргументов в конструкции in .

Решение проблем с несовпадающими строками

При наличии сложного запроса, использующего сразу несколько таблиц, но не воз­вращающего при этом ни одной строки, выяснить причину такой проблемы можно сле дующим образом:

  • Протестируйте запрос с помощью EXPLAIN , чтобы проверить его на наличие явных ошибок.
  • Выберите только те столбцы, которые указаны в конструкции WHERE .
  • Удаляйте по одной таблице из запроса до тех пор, пока он не начнет возвращать хоть какие-нибудь строки. Если размер таблиц большой, то целесообразно указать в запросе limit 10.
  • Выполните SELECT для столбца, в котором должна была совпасть строка с послед ней удаленной из запроса таблицей.
  • При сравнении столбцов FLOAT или DOUBLE , числа в которых содержат дробные части, использовать сравнение на предмет равенства (=) нельзя. Это наиболее рас пространенная проблема в большинстве компьютерных языков, поскольку не все значения с плавающей запятой могут быть сохранены с максимальной точностью. В некоторых случаях устранить такую проблему помогает замена FLOAT на DOUBLE .
  • Если по-прежнему выяснить причину проблемы не удается, создайте минималь ный тест, запустить который можно с помощью mysql test < query . sql и кото рый отобразит имеющиеся ошибки. Создать тестовый файл можно, выполнив дамп таблиц посредством mysqldump - quick имя_базы_данных имятаблицы1 ... имя_таблицы_п > query . sql . Откройте файл в редакторе, удалите некоторые строки со вставками (если их больше, чем требуется для отображения проблемы) и добавьте в конец файла свой оператор SELECT .

Удостоверьтесь, что тестовый файл иллюстрирует проблему, выполнив следую щие команды:

shell> mysqladmin create test2 shell> mysql test2 < query.sql

С помощью mysqlbug отправьте тестовый файл в общий список рассылки MySQL .
Проблемы при сравнении чисел с плавающей запятой

Числа с плавающей запятой иногда доставляют неприятности, поскольку как точные значения внутри компьютерной архитектуры они не хранятся. То, что отображается на экране, обычно не является точным значением числа. К типам столбцов, которые хранят числа с плавающей запятой, относятся FLOAT , DOUBLE и DECIMAL . Столбцы DECIMAL хранят максимально точные значения, поскольку значения в них представлены в виде строк, но вот вычисления по значениям из столбцов DECIMAL могут выполняться с использованием операций с плавающей запятой.

Следующий пример иллюстрирует проблему. Он показывает, что даже для столбцов типа DECIMAL вычисления, осуществляемые посредством операций с плавающей запятой, не исключают ошибок с плавающей запятой.

mysql> CREATE TABLE tl (i INT, dl DECIMAL(9,2) , d2 DECIMAL(9,2));

mysql> INSERT INTO tl VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), -> (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), -> (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), -> (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), -> (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), -> (6, 0.00, 0.00), (6, -51.40, 0.00);

mysql> SELECT i, SUM(dl) AS a, SUM(d2) AS b -> FROM tl GROUP BY i HAVING а О b;

i la | b |

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

  • | 21.40 | 21.40 |
  • | 76.80 | 76.80 |
  • | 7.40 | 7.40 |
  • | 15.40 | 15.40 |

5 - | 7.20 | 7.20 |
I 6 | -51.40 | 0.00 |
+ ------------- + ------------ +. ----------- +

Результат правильный. Хотя первые пять записей выглядят так, будто они не должны успешно проходить тест на сравнение (значения а и b разными не кажутся), однако они проходят его, поскольку разница между числами проявляется приблизительно на уровне десятого знака, в зависимости от архитектуры компьютера.

Устранить проблему с помощью ROUND () или других подобных функций нельзя, по скольку результат будет по-прежнему представлять собой число с плавающей запятой.

mysql> SELECT i, ROUND(SUM(dl), 2) AS a, ROUND(SUM(d2), 2) AS b -> FROM tl GROUP BY i HAVING a <> b;

< img src="/bimages/mysql/img121.gif">

Так выглядят числа в столбце при отображении большего количества знаков после запятой:

mysql> SELECT i, ROUND(SUM(dl), 2)*1.0000000000000000 AS a,

-> ROUND(SUM(d2), 2) AS b FROM tl GROUP BY i HAVING a <> b;

i I a | b
--------- +. ----------------------------------- + -----------

1 | 21.3999999999999986 I 21.40


А .1. Проблемы , связанные с запросами 409

| 2 | 76.7999999999999972 | 76.80 |

[ 3 | 7.4000000000000004 | 7.40 |

[ 4 | 15.4000000000000004 | 15.40 |

[ 5 | 7.2000000000000002 | 7.20 |

I 6 | -51.3999999999999986 | 0.00 |

+ --------- +. ----------------------------------- + ----------- j .

Увидите вы подобные результаты или нет, будет зависеть от архитектуры вашего компьютера. Разные центральные процессоры могут выполнять операции с плавающей запятой по-разному. Например, на некоторых машинах получить "корректные" результаты можно, умножив оба аргумента на 1, как показано в следующем примере.

i Внимание !

Щ Никогда не используйте этот метод в своих приложениях . Это отнюдь не пример надежного ме - V . тода !

mysql> SELECT i, ROUND (SUM (dl), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b -> FROM tl GROUP BY i HAVING а О b;

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

I i la | b |
+. --------- + ----------------- + ------------- +

I 6 | -51.40 | 0.00 |

Кажется, что приведенный выше пример должен работать. Причина этого заключает ся в том, что на той определенной машине, на которой проводился тест, центральный процессор во время операций с плавающей запятой округляет числа до одинакового значения. Однако правила, что так должен делать любой центральный процессор, не существует, поэтому на такой метод полагаться нельзя.

Правильным для сравнения чисел с плавающей запятой будет сначала определиться с допустимой степенью различия между числами, а затем выполнить операцию сравнения с учетом такого допустимого значения. Например, приняв договоренность, что числа с плавающей запятой должны рассматриваться как одинаковые, если они равны с точно стью до одной десятитысячной (0,0001), сравнение для выявления чисел, превышающих допустимый уровень отклонения, будет выполняться следующим образом:

mysql> SELECT i, SUM(dl) AS a, SUM(d2) AS b FROM tl -> GROUP BY i HAVING ABS(a - b) > 0.0001;

I 6 | -51.40 | 0.00 |
+ ------------ + ------------ + --------- +

1 row in set (0.00 sec)

И наоборот, если нужно получить строки, в которых числа являются одинаковыми, тест должен находить отличия в пределах допустимого значения отклонения:

mysql> SELECT i, SUM(dl) AS a, SUM(d2) AS b FROM tl -> GROUP BY i HAVING ABS(a - b) <= 0.0001;

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

I i l a | b |

+ --------- + ----------- + ---------- 1

I 1 t 21.40 I 21.40 I

| 76.80 | 76.80 |

| 7.40 | 7.40

4 | 15.40 | 15.40
5| 7.20 | 7.20