您可以在一条语句中访问 MySQL 中的自动增量值吗？

Question

提问by TheKeys

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

我有一个包含用户表的 MySQL 数据库。表的主键是'userid'，它被设置为一个自增字段。

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

我想要做的是，当我向表中插入新用户时，使用与自动增量在不同字段“default_assignment”中的“userid”字段中创建的值相同的值。

e.g.

例如

I'd like a statement like this:

我想要这样的声明：

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

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

所以我创建了用户 'Barry'，'userid' 生成为 16（例如），但我也希望 'default_assignment' 具有相同的值 16。

Is there any way to achieve this please?

请问有什么办法可以实现吗？

Thanks!

谢谢！

Update:

更新：

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

感谢您的回复。default_assignment 字段不是多余的。default_assigment 可以引用 users 表中的任何用户。创建用户时，我已经有一个允许选择另一个用户作为 default_assignment 的表单，但是在某些情况下需要将其设置为同一用户，因此我提出了问题。

Update:

更新：

Ok, I've tried out the update triggers suggestion but still can't get this to work. Here's the trigger I've created:

好的，我已经尝试了更新触发器建议，但仍然无法使其正常工作。这是我创建的触发器：

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

When inserting a new user however the default_assignment is always set to 0.

然而，在插入新用户时，default_assignment 始终设置为 0。

If I manually set the userid then the default_assignment does get set to the userid.

如果我手动设置用户 ID，那么 default_assignment 确实会设置为用户 ID。

Therefore the auto assignment generation process clearly happens after the trigger takes effect.

因此，自动分配生成过程显然发生在触发器生效之后。

Answer 1

回答by Resegue

there's no need to create another table, and max() will have problems acording to the auto_increment value of the table, do this:

不需要创建另一个表，并且 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

I declare the next_id variable because usually it will be used in some other way(*), but you could do straight new.field=(select ...)

我声明 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 ) ) );

Answer 2

回答by Navrattan Yadav

try this

尝试这个

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

Answer 3

回答by pilif

seeing that last_insert_id()wouldn't work in this case, yes, the trigger would be the only way to accomplish that.

看到last_insert_id()在这种情况下不起作用，是的，触发器将是实现这一目标的唯一方法。

I do ask myself though: What do you need this functionality for? Why do you store the users id twice? Personally, I don't like storing redundant data like this and I'd probably solve this in application code by making that ominous default_assignmentcolumn NULL and using the user id in my application code if default_assignmentwas NULL.

不过我确实问自己：你需要这个功能做什么？为什么要存储两次用户 ID？就我个人而言，我不喜欢像这样存储冗余数据，我可能会在应用程序代码中解决这个问题，方法是将该default_assignment列设为 NULL 并在我的应用程序代码中使用用户 ID（如果default_assignment为 NULL）。

Answer 4

回答by Sias Mey

Actually I just tried to do the same thing as was suggested above. But it seems Mysql doesent generate the inserted ID before the row actually gets commited. So NEW.userid will always return 0 in a Before insert trigger.

实际上，我只是尝试做与上面建议的相同的事情。但在行实际提交之前，Mysql 似乎没有生成插入的 ID。因此 NEW.userid 将始终在插入前触发器中返回 0。

The above also wont work unless it is a BEFORE INSERT trigger, since you cant update values in a AFTER INSERT query.

除非它是 BEFORE INSERT 触发器，否则上述内容也不起作用，因为您无法更新 AFTER INSERT 查询中的值。

From a Mysql Forum Post It seems the only way to handle this is using an additional table as a sequence. So that your trigger can pull in the values from an external source.

来自 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

This was copied from forums.mysql.com/read.php?99,186171,186241#msg-186241 but Im not allowed to post links yet.

这是从forums.mysql.com/read.php?99,186171,186241#msg-186241复制的，但我还不允许发布链接。

Answer 5

回答by Sias Mey

The only I found that would solve this problem without an extra table would be to calculate self the next number and put that in the fields required.

我发现唯一可以在没有额外表格的情况下解决这个问题的方法是计算 self 下一个数字并将其放入所需的字段中。

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;

Answer 6

回答by cronoklee

You can do this reliably using a simple subquery:

您可以使用简单的子查询可靠地做到这一点：

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

Answer 7

回答by jeffbuhrt

I tested the above trigger idea with 10 concurrent threads doing inserts and I got over 1000 cases of 2 or 3 duplicates after ~25k inserted.

我用 10 个并发线程进行插入测试了上述触发器的想法，并且在插入 ~25k 后我得到了超过 1000 个 2 或 3 个重复的案例。

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

I used 10 of:

我用了 10 个：

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

And ran the last query to watch for duplicates

并运行最后一个查询以观察重复项

example output: '3', '4217' '3', '13491' '2', '10037' '2', '14658' '2', '5080' '2', '14201' ...

示例输出： '3', '4217' '3', '13491' '2', '10037' '2', '14658' '2', '5080' '2', '14201' ...

Note 'LOCK IN SHARE MODE' didn't change anything. With and without gave duplicates at about the same rate. It seems that MySQL AUTO_INCREMENT doesn't work like Postgres' next_val() and is NOT concurrency safe.

注意“锁定共享模式”没有改变任何东西。有和没有以大致相同的速度重复。似乎 MySQL AUTO_INCREMENT 不像 Postgres 的 next_val() 那样工作，并且不是并发安全的。

Answer 8

回答by Vinzz

I know this post is from 2010, but I couldn't find a good solution. I've solved this by creating a separate table that holds the counters. When I need to generate an unique identifier for a column I just call a Stored proc:

我知道这篇文章是 2010 年的，但我找不到好的解决方案。我通过创建一个单独的表来保存计数器解决了这个问题。当我需要为列生成唯一标识符时，我只需调用存储过程：

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

The 'for update' statement locks the row in the counters table. This avoids duplicates being made by multiple threads.

'for update' 语句锁定计数器表中的行。这避免了多个线程进行重复。

Answer 9

回答by Dani-Br

basically, the solution is like Reseguesaid.
But if you want it in one statement, you will use one of the below ways:

基本上，解决方案就像Resegue所说的那样。
但是，如果您想在一个语句中使用它，您将使用以下方法之一：

1.One long statement:

1.一个长声明：

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

or for text with number:

或带有数字的文本：

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)))

it looks more clearly in PHP:

它在 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.Using function: (not tested yet)

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'))

Answer 10

回答by George

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

您可以在一条语句中访问 MySQL 中的自动增量值吗？

提问by TheKeys

回答by Resegue

回答by Navrattan Yadav

回答by pilif

回答by Sias Mey

回答by Sias Mey

回答by cronoklee

回答by jeffbuhrt

回答by Vinzz

回答by Dani-Br

回答by George

相关推荐

最近更新

标签

您可以在一条语句中访问 MySQL 中的自动增量值吗？

提问by TheKeys

回答by Resegue

回答by Navrattan Yadav

回答by pilif

回答by Sias Mey

回答by Sias Mey

回答by cronoklee

回答by jeffbuhrt

回答by Vinzz

回答by Dani-Br

回答by George

相关推荐

MySQL MySQL外键允许NULL吗？

MySQL 在一个结果行中选择多行

MySQL AUTO_INCREMENT 不回滚

在插入行时返回最后一个 ID (IDENTITY) VB.NET MySQL

相关推荐

最近更新

标签