InnoDB full-text search

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

Поиск пропусков в таблице

У нас тут сегодня на работе возникла гипотетическая потребность найти пропуски в конкретной таблице базы данных. Так как я кроме как с 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», который там только затем, чтобы решение получилось в одну строку.

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

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

int(?) в MySQL

Кто уверен, что точно знает что означает тип «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. Кто за вами потом разгадает, получит удовольствие.
4 комментария
25 сентября 2012 07:59

Внимание: MySQL считает, что буквы «ё» и «е» — одно и то же.

Увы, 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». Это полностью решает проблему с «ё», но лишает нас удовольствия искать, не заботясь о совпадении регистра.
18 комментариев
31 января 2011 16:34

MySQL и строки

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

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

SELECT _binary'aaa' = _binary'AAA' -- эти строки не равны (бинарное сравнение)
Комментировать
2 декабря 2010 14:36

Mongo vs. MySQL

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

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

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

XML в MySQL

Почему-то мало кто слышал, что в 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).
6 комментариев
24 декабря 2009 23:15

MySQL 5.1 не готов

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.

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

A Quick Look at MySQL 6.0’s New Backup

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

Sun MySQL

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

Оптимизация MySQL InnoDB

Давно хотел записать, чтобы не потерять. Выдержка из главы «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 могут сильно фрагметироваться в процессе работы, для того, чтобы избавиться от фрагметрации выполняйте иногда это «магическую» конструкцию или снимайте дамп с таблицы и заливайте обратно.

Пример скрипта для автоматической дефрагментации — дальше. P.S. Как справедливо заметил Jimi Dini, в MySQL 5.0 и выше OPTIMIZE TABLE делает то же самое для InnoDB.
9 комментариев
9 ноября 2007 17:07

[Без заголовка]

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

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


"Благодаря... не поддерживается"? Не вижу причин благодарить.
Комментировать
24 ноября 2004 19:00