MySQL: неожиданный результат SELECT после INSERT в таблицу со столбцом автоинкремента

Я наблюдаю странное поведение, когда я ВСТАВЛЯЮ некоторые данные в таблицу, а затем запускаю запрос SELECT для той же таблицы. Эта таблица имеет первичный ключ с автоинкрементом (uid), и эта проблема возникает, когда я пытаюсь затем выбрать результаты, где «uid IS NULL».

Я свел это к следующим командам SQL:

DROP TABLE IF EXISTS test_users;
CREATE TABLE test_users (uid INTEGER PRIMARY KEY AUTO_INCREMENT, name varchar(20) NOT NULL);
INSERT INTO test_users(name) values('foo');
SELECT uid FROM test_users WHERE uid IS NULL;
SELECT uid FROM test_users WHERE uid IS NULL; -- no output from this query

Я ожидаю, что SELECT uid FROM test_users WHERE uid IS NULL никогда ничего не вернет, но иногда это происходит. Вот что я нашел:

  • Версия MySQL/MariaDB, кажется, имеет значение. На машине с этой проблемой работает MySQL 5.1.73 (CentOS 6.5, как 32-разрядная, так и 64-разрядная версии). На другой моей машине работает 5.5.37-MariaDB (Fedora 19, 64-разрядная версия). Оба работают с конфигурациями по умолчанию, кроме того, что они настроены на использование таблиц MyISAM.
  • Затрагивается только первый запрос SELECT после INSERT.
  • Если я укажу значение для uid, а не позволю ему автоматически увеличиваться, тогда все в порядке.
  • Если я отключаюсь и снова подключаюсь между INSERT и SELECT, я не получаю ожидаемых результатов. Это легче всего увидеть в чем-то вроде Perl, где я управляю объектом соединения. У меня есть тестовый скрипт, демонстрирующий это по адресу https://gist.github.com/avuserow/1c20cc03c007eda43c82.

mysql mariadb
person avuserow    schedule 15.05.2014    source источник
comment
Это происходит и в InnoDB?   -  person Marcus Adams    schedule 16.05.2014

Ответы (1)


arrow_upward
0
arrow_downward

Такое поведение является особенностью.

Это, очевидно, эквивалентно SELECT * FROM t1 WHERE id = LAST_INSERT_ID();, который также будет работать только из соединения, в которое вы только что вставили, точно так, как вы описали.

По-видимому, это обходной путь, который вы можете использовать в некоторых средах, которые затрудняют получение значения автоинкремента последней вставленной (по вашему соединению) строки более обычным способом.

Точнее, на самом деле это значение auto_increment, присвоенное первой строке, вставленной оператором последней вставки вашего соединения. Это то же самое, когда вы вставляете только одну строку, но это не то же самое, когда вы вставляете несколько строк с помощью одного оператора вставки.

http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-functionality-last-insert-id.html

person Michael - sqlbot    schedule 16.05.2014
comment
Это также, по-видимому, переменная сеанса в MySQL 5.1, поэтому я должен установить ее после установления соединения. Ничего страшного здесь нет, но стоит знать. - person avuserow; 16.05.2014
comment
Да, @@last_insert_id — это переменная сеанса, которая используется для хранения значения, возвращаемого функцией LAST_INSERT_ID(). Предположительно, это было сделано таким образом, чтобы упростить реализацию воспроизведения бинарных журналов: эта переменная сеанса на самом деле доступна для записи... хотя установка ее после установления соединения или выбор значения null из необнуляемого столбца - это необычно. Кроме того, обновленный ответ стал более точным: когда вы вставляете несколько строк в один оператор вставки, здесь сохраняется первое из назначенных значений auto_increment. - person Michael - sqlbot; 16.05.2014