Можете ли вы получить доступ к значению автоматического увеличения в MySQL в одном выражении?

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

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

например.

Мне бы хотелось сделать следующее выражение:

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

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

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

Спасибо!

Update:

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

Update:

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

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

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

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

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

Ответ 1

нет необходимости создавать другую таблицу, а 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 ) ) );

Ответ 2

попробуйте это

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

Ответ 3

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

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

Ответ 4

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

Вышеупомянутое также не будет работать, если оно не является триггером FOREX 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, но Im не разрешено публиковать ссылки.

Ответ 5

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

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;

Ответ 6

в основном, решение похоже на 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'))

Ответ 7

$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'];
}

Ответ 8

Вы можете сделать это надежно, используя простой подзапрос:

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

Ответ 9

Я протестировал вышеупомянутую триггерную идею с 10 параллельными потоками, делающими вставки, и я получил более 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 не работает, как post_res (post_res) Postgres и не является безопасным concurrency.

Ответ 10

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

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

Оператор 'for update' блокирует строку в таблице счетчиков. Это позволяет избежать дублирования, создаваемого несколькими потоками.