mysql

Вчера при импорте на сайт реакций из «Телеграма» заметил какой-то странный баг — количество реакций совпало, а их состав — нет. В «Телеграме» две пары «глаз» и «палец вниз», а у меня на сайте — только глаза.

Заметил, что реации из «Телеграма» импортируются неправильно

Пошёл разбираться, выяснилось, что в базе всё лежит отдельно, как положено. Нейросеть «Сол» обнаружила странное — несмотря на то, что эмодзи «глаз» и «палец» имеют разные коды, «МайЭсКьюЭль» считает их одним значением и объединяет при группировке.

Вот тут «Сол» меня сильно расстроил. Сначала он предложил группировать через функцию HEX() . Решение само по себе плохое, но проблема ещё и в том, что движок сайта поддерживает три СУБД, и в «Постгресе» такой функции нет, а генерировать разные запросы под базы не очень хочется.

Тогда он решил считать хеш через PHP, чтобы группировать по нему. К сожалению, в этот момент я не следил за окном, где он копался, и он успел это реализовать и даже задеплоить.

Меня такая реализация категорически не устроила. Мне в целом понятно, что проблема связана с тем, что называется «collate» — в СУБД это набор правил, по которым сравниваются строки. У «МайЭсКьюЭля» много особенностей при работе с Юникодом, с которыми я уже сталкивался, поэтому был уверен, что дело именно в одной из них.

Готовый рецепт нашёлся безо всяких проблем. Чтобы эмодзи сравнивались правильно, нужно установить «collate» для этого столбца как utf8mb4_bin, что мы и сделали. Не думаю, что это необходимо, тем не менее для остальных баз мы выставили в этом месте бинарный «collate» — С для «Постгреса» и BINARY для «ЭскуэльЛайта».

Хорошие новости, оказывается, с версии 5.6.4 MySQL поддерживает полнотекстовые поиск в InnoDB (12 лет прошло с момента появления этого движка в Майэскюэле) и, кстати, в версии 5.5 и выше по-умолчанию для создания таблиц выбирается именно InnoDB.

У нас тут сегодня на работе возникла гипотетическая потребность найти пропуски в конкретной таблице базы данных. Так как я кроме как с MySQL в последнее время ни с чем не имел, прикинул как бы я решил эту задачу там. Получилось что-то такое:

SELECT id FROM (
    SELECT id, id-@prev AS diff, @prev:=id
    FROM
        (SELECT @prev:=NULL) ``,
        sourceofdata
   ORDER BY id
) `` WHERE diff>1

Внутри есть напрасный запрос «SELECT @prev:=NULL», который там только затем, чтобы решение получилось в одну строку.

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

Может кто-то придумать проще? Не важно для какой СУБД.

Кто уверен, что точно знает что означает тип «int(11)» в MySQL? Я именно «интами» не пользовался никогда, всегда заводил через tinyint и т. п., поэтому, глядя на то СУБД в эти скобочки сама подставляет какие-то значения, был уверен, что это размер в каких-то попугаях. Руки не доходили проверить в каких.

Оказывается, это размер да не тот:

int(i) i — это не ограничение разрядности, int всегда одинаков -2³¹ до +2³¹-1 (или 0 до 2³²-1), а ограничение количества показываемых символов при отображении столбца, его значение от 1 до 255. Например, INT(5) объявляет столбец целого типа который при отображении будет выводиться в колонках по пять цифр.

Если в столбце реально записано значение которое нельзя отобразить 5-тью цифрами то оно будет отображено большим количеством символов, т. е. при выводе значение не усекается.

Так что будете заводить в следующий раз таблицу в MySQL, где идут подряд пять целых чисел, задайте им размерности 72, 101, 108, 108 и 111. Получится по кодам «Hello» в кодировке ASCII. Кто за вами потом разгадает, получит удовольствие.

Увы, MySQL, если выбрать кодировку Unicode, считает, что «е» и «ё» — это одно и то же. Багом это не считается, в официальной таблице, где описано как MySQL 6.0 сравнивает символы, эти символы в одном ряду.

mysql> set names utf8 collate utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'е'='ё', 'ё'='ѐ'\G
*************************** 1. row ***************************
'е'='ё': 1
'ё'='ѐ': 1
1 row in set (0.00 sec)

Самое печальное, это нелогичное поведение всплывает, если есть потребность перевести базу с CP1251 на UTF-8, так как в CP1251-то как раз всё хорошо:

mysql> set names cp1251;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'е'='ё', 'ё'='ѐ'\G
*************************** 1. row ***************************
'е'='ё': 0
'ё'='ѐ': 0
1 row in set (0.00 sec)

Чем это может быть плохо? Например, у нас по какому-то текстовому полю имеется уникальный ключ. Для иллюстративности, пусть это будет словарь фамилий. Фамилии Рублев и Рублёв — разные, но MySQL так не считает (для простоты опускаю другие поля):

mysql> set names utf8 collate utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> create table surname(surname varchar(255) primary key);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into surname values('Рублёв');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into surname values('Рублев');
ERROR 1062 (23000): Duplicate entry 'Рублев' for key 'PRIMARY'

Я придумал только один способ, исправляющий это поведение (но у него есть недостаток):

mysql> create table surname(surname varchar(255) primary key collate 'utf8_bin');
Query OK, 0 rows affected (0.02 sec)

mysql> insert into surname values('Рублёв');
Query OK, 1 row affected (0.00 sec)

mysql> insert into surname values('Рублев');
Query OK, 1 row affected (0.00 sec)

mysql> select * from surname where surname='рублев';
Empty set (0.00 sec)

Я выключил case insensitive у данного столбца, явным указанием «collate». Это полностью решает проблему с «ё», но лишает нас удовольствия искать, не заботясь о совпадении регистра.

Век живи, век учись.

Не знал, что в MySQL есть своеобразный typecast для строк — можно объявить любой collate для строки вот таким простым синтаксисом (я всега пользовался «’строка’ COLLATE latin1_danish_ci» и ему подобным):

SELECT n'текст' -- эквивалентно следующей строке
SELECT _utf8'текст'

SELECT _binary'aaa' = _binary'AAA' -- эти строки не равны (бинарное сравнение)

Сравнение скорости MongoDB и MySQL на вставку и чтение. Как видно из данных, MongoDB в 3­-4 раза быстрее, чем MySQL на запись, но чуть медленнее на чтение.

Конечно, это достаточно сферические тесты, но голые чтение/запись тоже интересны.

На графу «Mongo cursor (WRONG)» внимания не обращайте, это ошибочные данные.

Почему-то мало кто слышал, что в MySQL с версии 5.1.5 появился «чудесный» способ нарушать 1NF, при этом работая с частями поля таблицы, как со сложной структурой.

Кавычки вокруг слова «чудесный» какбэ намекают, что я не очень-то одобряю этот шаг, хотя понимаю, что иногда он оправдан. Просто я понимаю его опасность — можно увлечься этой возможность и забыть чем грозит нарушение первой нормальной формы.

Речь идёт о двух функциях — ExctractValue и UpdateXML. Если в поле таблицы хранится XML, то первая функция позволит выбрать данные при помощи XPath, а вторая — (при помощи XPath же) изменить какую-то их часть. Ничего неожиданного — функции могут работать как с полями таблицы, так и со строкой, переменной, результатом функции или любой комбинацией вышеперечисленного.

Примерно так:

SELECT ExtractValue('<a>c</a><b/>', '//a');

SELECT UpdateXML('<a><b>ccc</b><d></d></a>', '/a', '<e>fff</e>');

SELECT * FROM users WHERE ExtractValue(info, '//firstname') == 'Иван' AND ExtractValue(info, '//surname') == 'Сагалаев';

В случае, если XPath не дал результата или в поле оказался не XML, функции вернут NULL (версии MySQL до 5.1.12 содержат баг и частично могут работать даже с not-well-formed XML).

MySQL 5.1 ставить на production нельзя:

We still have 20 known and tagged crashing and wrong result bugs in 5.1 35 more if we add the known crashing bugs from 5.0 that are likely to also be present in 5.1.

We still have more than 180 serious bugs (P2) in 5.1. Some of these can be found here.

We have more than 300 known and verified less critical bugs that are not going to be addressed soon. (The total reported number of bugs to the MySQL server is of course much larger

Пишет о свежем релизе MySQL 5.1 Michael Widenius, «родитель» MySQL, который занимает сейчас пост технического директора в MySQL AB.

Для тех, кто не читает по-английски: всё очень плохо, много критических багов, продукт сырой, падает.

На сайте для разработчиков под MySQL появилась интересная статья «A Quick Look at MySQL 6.0’s New Backup», о том что есть в MySQL 6.0 для резервного копирования данных. Вообще в MySQL это было больное место, особенно на нагруженных проектах — как сделать резервное копирование без остановки проекта, наконец что-то делается в этом направлении.

Уже все интересующиеся в курсе, что Sun купила MySQL AB? Сумма сделки — 800 миллионов долларов США и 200 миллионов в качестве опциона. Людям особенно интересно что будет с InnoDB, который принадлежит Oracle и, по сути, является единственным нормальным движком в MySQL. Конечно, MySQL разрабатывали очень хороший storage Falcon, но до его завершения ещё далеко.

Давно хотел записать, чтобы не потерять. Выдержка из главы «Defragmenting a Table» руководства по MySQL:

It can speed up index scans if you periodically perform a “null” ALTER TABLE operation: ALTER TABLE tbl_name ENGINE=INNODB

That causes MySQL to rebuild the table. Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

По-русски говоря, таблицы в формате InnoDB могут сильно фрагметироваться в процессе работы, для того, чтобы избавиться от фрагметрации выполняйте иногда это «магическую» конструкцию или снимайте дамп с таблицы и заливайте обратно.

mysql_connect('localhost', 'root', 'пароль');

$res = mysql_query('SHOW DATABASES');

while (list($dbname) = mysql_fetch_row($res))
{
        mysql_select_db($dbname);

        $rec = mysql_query('SHOW TABLE STATUS');

        while ($data = mysql_fetch_object($rec))
        if ($data->Engine == 'InnoDB')
        {
                echo 'Processing ', $dbname, '.', $data->Name, ' ... '; flush();

                mysql_query("ALTER TABLE {$data->Name} ENGINE=InnoDB");

                echo "done.n";
        }

        mysql_free_result($rec);
}

mysql_free_result($res);

Фраза из руководства по MySQL (вариант руководства на русском):

Благодаря этому удобному синтаксису, в сервере MySQL не поддерживается оператор ANSI SQL || для конкатенации строк: вместо него используется функция CONCAT().

«Благодаря… не поддерживается»? Не вижу причин благодарить.

В интернете есть слухи о вложенных запросах в MySQL 3.xx. Кто-нибудь может подтвердить или опровергнуть? Вообще, мне верится достаточно легко. Вполне может быть, что разрабочики MySQL, как это у них обычно водится, решили приделать очередной костыль. Но передумали.