Несколько заметок о MySQL

За время работы с MySQL набралось некоторое количество нюансов и приемов, из которых я составил эту статью в виде набора заметок. Все это не секрет и, разумеется, можно найти в документации.

Буду использовать дефолтные настройки MySQL. Некоторые заметки связаны с PHP, поэтому для примеров буду использовать расширение mysqli.

Для запуска sql-запросов из статьи можно инициализировать таблицы так:

CREATE TABLE IF NOT EXISTS `user_myisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(8) NOT NULL,
  `money` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `user_myisam` (`id`, `login`, `money`) VALUES
(1, 'ivanov', 100),
(2, 'petrov', 200),
(3, 'sidorov', 300);

CREATE TABLE IF NOT EXISTS `user_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(8) NOT NULL,
  `money` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `user_innodb` (`id`, `login`, `money`) VALUES
(1, 'ivanov', 100),
(2, 'petrov', 200),
(3, 'sidorov', 300);

Проверить текущие автоинкременты можно так:

SHOW TABLE STATUS;

У обоих таблиц они равны 4.

При этом имеется такое подключение к БД:

$mysqli = new mysqli($host, $user, $password, $database);

Вставка по уникальному ключу и автоинкремент

Если в таблице есть уникальный ключ, то для вставки и обновления есть три способа переложить проверку уникальности на MySQL: INSERT IGNORE, INSERT… ON DUPLICATE KEY UPDATE, REPLACE. Каждый тип запросов по разному ведет себя с автоинкрементом на разных типах таблиц:

INSERT IGNORE INTO `user_innodb` SET `login` = "ivanov", `money` = 1000; 

Автоинкремент стал 5, хотя вставки не было.

А что будет с MyISAM:

INSERT IGNORE INTO `user_myisam` SET `login` = "ivanov", `money` = 1000; 

Там автоинкремент остался 4.

Аналогичная ситуация будет с ON DUPLICATE KEY URPDATE:

INSERT INTO `user_innodb` SET `login` = "ivanov", `money` = 1000 ON DUPLICATE KEY UPDATE `money` = 1000; 
INSERT INTO `user_myisam` SET `login` = "ivanov", `money` = 1000 ON DUPLICATE KEY UPDATE `money` = 1000; 

В user_innodb автоинкремент стал 6, а у user_myisam остался 4.

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

REPLACE `user_innodb` SET `login` = "petrov", `money` = 2000;

Увеличит автоинкремент до 7, теперь у Петрова id = 6.

У MyISAM аналогично:

REPLACE `user_myisam` SET `login` = "petrov", `money` = 2000; 

Автоинкремент стал 5, а Петров получил id = 4.

Итак, REPLACE работает на обоих движках таблиц одинаково, а INSERT IGNORE и ON DUPLICATE KEY UPDATE изменяют автоинкремент на InnoDB.
Про автоинкремент и про нюансы с InnoDB.

Получение id изменяемой записи после обновления

После вставки/обновления, с использованием INSERT… ON DUPLICATE KEY UPDATE, $mysqli->insert_id содержит id только если произошло добавление записи. Если нужно вытащить id изменяемой записи независимо от того была вставка или редактирование, можно сделать так:

$mysqli->query('INSERT INTO `user_innodb` SET `login` = "walker" ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `money` = 10;'); 
echo $mysqli->insert_id; 

$mysqli->query('INSERT INTO `user_innodb` SET `login` = "walker" ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `money` = 20;'); 
echo $mysqli->insert_id;

Выведет 7 и 7, первый раз запись была добавлена под id = 7, второй раз изменена.

С INSERT IGNORE такой трюк не выйдет. Код ниже выведет 9 и 0

$mysqli->query('INSERT IGNORE `user_innodb` SET `id` = LAST_INSERT_ID(`id`), `login` = "smith", `money` = 3000'); 
echo $mysqli->insert_id;

$mysqli->query('INSERT IGNORE `user_innodb` SET `id` = LAST_INSERT_ID(`id`),`login` = "smith", `money` = 5000'); 
echo $mysqli->insert_id;

Реализация SEQUENCE

У MySQL, в отличие от других СУРБД, нет такой штуки как SEQUENCE. Есть автоинкремент, но он не позвоялет решить все задачи, с которыми может помочь SEQUENCE. Например, шардинг.
В общем случае, если надо раскладывать записи по различным таблицам или даже базам, нужна будет мастер-таблица с автоинкрементным полем, в котором централизованно генерировался бы ID новой записи.

Решить эту задачу можно так:

CREATE TABLE IF NOT EXISTS `sequence` (
  `user_id` int(11) NOT NULL DEFAULT '0',
  `post_id` int(11) NOT NULL DEFAULT '0',
  `some_other_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sequence` (`user_id`, `post_id`, `some_other_id`) VALUES (0, 0, 0);

В одной таблице получается сразу несколько последовательностей, в этом примере три.

Далее можно получать следующий ID из нужной последовательности с помощью функции
last_insert_id():

UPDATE `sequence` SET `some_other_id` = last_insert_id(`some_other_id` + 1);
SELECT last_insert_id();

Для автоинкремента есть возможность шаг приращения опцией конфигурации auto_increment_increment. В это примере такую функию можно реализовать примерно так:

UPDATE `sequence` SET `some_other_id` = last_insert_id(`some_other_id` + N);
SELECT last_insert_id();

Немного о беззнаковых целых

С аккуратностью используйте беззнаковые целые как типы полей MySQL, если обращаетесь к БД из PHP.

История моего «озарения» по этому поводу. Для поля id всегда использовал беззнаковый целый тип, все равно классический id не бывает отрицательным. Однажды, генерируя модель с помощью Gii (скаффолдинг Yii), я обратил внимание, на то что правила валидации в модели для моих id и других беззнаковых целых полей генерируются как для строк. “WTF?” — подумал я и полез в код фреймворка, где обнаружил, что при разборе типов полей есть такой “хардкод” проверки на наличие unsigned:

if(stripos($dbType,'int')!==false && stripos($dbType,'unsigned int')===false)

Я посчитал это ошибкой, обрадовался, что сейчас у меня есть шанс внести свою лепту в исправление багов Yii. Но радость быстро сменилась мыслью “это ж-ж-ж-ж, неспроста”.

Действительно, в PHP нет беззнаковых целых, а в общем случае целые в PHP 32-х разрядные (под 32-bit Linux и под Windows). Если целочисленное значение превышает PHP_INT_MAX, то оно приводится к float, и тут самое место для возникновения магии со странными багами. Так что господин Qiang Xue все правильно сделал.

Автор: maximw

Источник