Оператор «для обновления» блокирует строку в таблице счетчиков. Это позволяет избежать дубликатов, создаваемых несколькими потоками.

я есть база данных MySQL, которая содержит таблицу пользователей. Первичным ключом таблицы является «идентификатор пользователя», который устанавливается как поле с автоматическим приращением.

Что я хотел бы сделать, так это когда я вставляю нового пользователя в таблицу, чтобы использовать то же значение, которое автоматическое увеличение создает в поле 'userid' в другом поле 'default_assignment'.

например

Я хотел бы заявление, как это:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

поэтому я создаю пользователя «Барри», «идентификатор пользователя» генерируется как 16 (например), но я также хочу, чтобы «default_assignment» имел то же значение 16.

Есть ли способ достичь этого, пожалуйста?

Спасибо!

Обновить:

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

Обновить:

Хорошо, я опробовал предложение триггеров обновления, но все еще не могу заставить это работать. Вот триггер, который я создал:

CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users`  
FOR EACH ROW BEGIN
SET NEW.default_assignment = NEW.userid;
END;

Однако при вставке нового пользователя default_assignment всегда устанавливается в 0.

Если я вручную установлю ID пользователя, тогда default_assignment будет установлен на ID пользователя.

Следовательно, процесс генерации автоматического назначения явно происходит после того, как срабатывает триггер.

 tvanfosson22 янв. 2009 г., 17:44
Вероятно, вы хотите, чтобы этот триггер запускал ПОСЛЕ вставки, а не ДО, так как поле ИД пользователя не имеет значения до окончания вставки.
 Resegue07 нояб. 2011 г., 23:57
попробуйте это: <code> CREATE TRIGGER default_assignment_self ПЕРЕД ВСТАВКОЙ ВКЛusers ДЛЯ КАЖДОЙ СТРОКИ НАЧИНАЕТСЯ УСТАНОВИТЬ NEW.default_assignment = (ВЫБЕРИТЕ AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE () AND TABLE_NAME = 'users'); END // </ код>

Ответы на вопрос(10)

$ret = $mysqli->query("SELECT Auto_increment FROM information_schema.tables WHERE table_schema = DATABASE() ");l
while ($row = mysqli_fetch_array($ret)) {
    $user_id=$row['Auto_increment'];
}

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

CREATE DEFINER=`root`@`localhost` PROCEDURE `IncrementCounter`(in id varchar(255))
BEGIN
declare x int;
-- begin;
start transaction;
-- Get the last counter (=teller) and mark record for update.
select Counter+1 from tabel.Counter where CounterId=id into x for update;
-- check if given counter exists and increment value, otherwise create it.
if x is null then
    set x = 1;
    insert into tabel.Counters(CounterId, Counter) values(id, x);
else
    update tabel.Counters set Counter = x where CounterId = id;
end if;
-- select the new value and commit the transaction
select x;
commit;
END

Оператор «для обновления» блокирует строку в таблице счетчиков. Это позволяет избежать дубликатов, создаваемых несколькими потоками.

Resegue сказал.
Но если вы хотите это в одном утверждении, вы будете использовать один из следующих способов:

1. Одно длинное утверждение:

INSERT INTO `t_name`(field) VALUES((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t_name'))

или для текста с номером:

INSERT INTO `t_name`(field) VALUES(CONCAT('Item No. ',CONVERT((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t_name') USING utf8)))

это выглядит более четко в PHP:

$pre_name='Item No. ';
$auto_inc_id_qry = "(SELECT AUTO_INCREMENT FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$table')";
$new_name_qry = "CONCAT('$pre_name',CONVERT($auto_inc_id_qry USING utf8))";
mysql_query("INSERT INTO `$table`(title) VALUES($new_name_qry)");

2. Использование функции: (еще не проверено)

CREATE FUNCTION next_auto_inc(table TINYTEXT) RETURNS INT
BEGIN
DECLARE next_id INT;
SELECT AUTO_INCREMENT FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=table INTO next_id;
RETURN next_id;
END

INSERT INTO users ('username','default_assignment')
    VALUES ('barry', next_auto_inc('users'))

попробуй это

INSERT INTO users (default_assignment) VALUES (LAST_INSERT_ID()+1);

ми, выполняющими вставки, и я получил более 1000 случаев с 2 или 3 дубликатами после вставки ~ 25k.

DROP TABLE IF EXISTS test_table CASCADE;

CREATE TABLE `test_table` (
  `id`             INT         NOT NULL  AUTO_INCREMENT,
  `update_me`      VARCHAR(36),
  `otherdata`      VARCHAR(36) NOT NULL,

  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COMMENT 'test table for trigger testing';

delimiter $

DROP TRIGGER IF EXISTS setnum_test_table;
$
CREATE TRIGGER setnum_test_table
BEFORE INSERT ON test_table FOR EACH ROW
-- SET OLD.update_me = CONCAT(NEW.id, 'xyz');

BEGIN
   DECLARE next_id INT;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='test_table' LOCK IN SHARE MODE );
--    SET NEW.update_me = CONCAT(next_id, 'qrst');
    SET NEW.update_me = next_id;
END

$

delimiter ;

-- SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='test_table'

INSERT INTO test_table (otherdata) VALUES ('hi mom2');

SELECT count(*) FROM test_table;
SELECT * FROM test_table;

-- select count(*) from (
select * from (
SELECT count(*) as cnt ,update_me FROM test_table group by update_me) q1
where cnt > 1
order by cnt desc

Я использовал 10 из:

while true ; do echo "INSERT INTO test_table (otherdata) VALUES ('hi mom2');" | mysql --user xyz testdb ; done &

И запустил последний запрос для поиска дубликатов

Пример вывода: «3», «4217», «3», «13491», «2», «10037», «2», «14658», «2», «5080», «2», «14201» ...

Примечание «LOCK IN SHARE MODE» ничего не изменило. С и без дали дубликаты примерно с одинаковой скоростью. Кажется, что MySQL AUTO_INCREMENT не работает как Postgres 'next_val () и НЕ безопасен для параллелизма.

last_insert_id() не сработает в этом случае, да, триггер будет единственным способом сделать это.

Я спрашиваю себя: зачем вам эта функциональность? Почему вы храните идентификатор пользователя дважды? Лично мне не нравится хранить такие избыточные данные, и я бы, вероятно, решил это в коде приложения, сделав это зловещимdefault_assignment столбец NULL и используя идентификатор пользователя в моем коде приложения, еслиdefault_assignment был НЕДЕЙСТВИТЕЛЕН.

Решение Вопроса

и max () будет иметь проблемы в соответствии со значением auto_increment таблицы, сделайте это:

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   DECLARE next_id INT;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
   SET NEW.field=next_id;
END

Я объявляю переменную next_id, потому что обычно она будет использоваться другим способом (*), но вы можете сделать прямо new.field = (select ...)

(*) To auto-name an image:
SET NEW.field = CONCAT('image_', next_id, '.gif');
(*) To create a hash:
SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );
 Pierre de LESPINAY29 апр. 2011 г., 16:16
Благодарим Вас за (ВЫБЕРИТЕ AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE () И TABLE_NAME = 'tbl'). У меня тоже хорошо работало
 Resegue07 нояб. 2011 г., 23:48
Multi-запись - это действительно проблема, пожалуйста, укажите лучшее решение, если можете.
 Resegue07 нояб. 2011 г., 23:43
При высококонкурентном использовании формата таблицы innoDB он блокирует результат подзапроса, избегая выбора одной строки автоинкрементом другого. Я провел некоторое тестирование в этом направлении, поместил 1200 запросов в случайные 10–1000 миллисекунд, получая 500 вставок в секунду, ограниченную пропускной способностью сервера.
 Lukas Eder12 сент. 2011 г., 16:36
Это не работает для систем с высокой степенью одновременности или для вставки нескольких записей ...
 István Ujj-Mészáros17 дек. 2010 г., 14:12
+1SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl'); сделал трюк для меня.

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

CREATE TABLE `Temp` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(255) ,
  PRIMARY KEY  (`idsequence`)
) ENGINE=InnoDB;


CREATE TRIGGER temp_before_insert BEFORE INSERT ON `Temp`
FOR EACH ROW 
BEGIN
    DECLARE m INT;

    SELECT IFNULL(MAX(id), 0) + 1 INTO m FROM Temp;
    SET NEW.value = m;
    -- NOT NEEDED but to be save that no other record can be inserted in the meanwhile
    SET NEW.id = m;   
END;
 Lukas Eder12 сент. 2011 г., 16:43
Это, кажется, самое простое решение, если вы не находитесь в параллельной ситуации, когда условия гонки дляMAX(id) может возникнуть функция

INSERT INTO users ('username','default_assignment')
    SELECT 'barry', Auto_increment FROM information_schema.tables WHERE TABLE_NAME='users'

что было предложено выше. Но, похоже, Mysql сгенерирует вставленный идентификатор до того, как строка будет зафиксирована. Поэтому NEW.userid всегда будет возвращать 0 в триггере перед вставкой.

Вышеприведенное также не будет работать, если это не триггер BEFORE INSERT, поскольку вы не можете обновить значения в запросе AFTER INSERT.

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

CREATE TABLE `lritstsequence` (
  `idsequence` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`idsequence`)
) ENGINE=InnoDB;

CREATE TABLE `lritst` (
`id` int(10) unsigned NOT NULL auto_increment,
`bp_nr` decimal(10,0) default '0',
`descr` varchar(128) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `dir1` (`bp_nr`)
) ENGINE=InnoDB;

DELIMITER $

DROP TRIGGER /*!50032 IF EXISTS */ `lritst_bi_set_bp_nr`$

CREATE TRIGGER `lritst_bi_set_bp_nr` BEFORE INSERT ON `lritst`
FOR EACH ROW
BEGIN
    DECLARE secuencia INT;
    INSERT INTO lritstsequence (idsequence) VALUES (NULL);
    SET secuencia = LAST_INSERT_ID();
    SET NEW.id = secuencia;
    SET NEW.bp_nr = secuencia;
END;$

DELIMITER ;

INSERT INTO lritst (descr) VALUES ('test1');
INSERT INTO lritst (descr) VALUES ('test2');
INSERT INTO lritst (descr) VALUES ('test3');

SELECT * FROM lritst;

Result:

    id   bp_nr  descr 
------  ------  ------
     1       1  test1 
     2       2  test2 
     3       3  test3

Это было скопировано с forums.mysql.com/read.php?99,186171,186241#msg-186241, но я пока не могу публиковать ссылки.

Ваш ответ на вопрос