dtncv04228
2013-11-13 21:57
浏览 84
已采纳

每个键值的循环自动增量

I have mysql table of some records, e.g.:

CREATE TABLE test (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value varchar NOT NULL
) 

now, what I need is to generate unique sequence of 1, 2, ..., N in php script and store to another table... How to achieve this to be thread-safe and not creating doubles or skipping something?

I was wondering if some additional mysql table could be helpful, but I don't know how to create something like "separate autoincrements for each column value" or anything else...

test:
1 ... apples
2 ... oranges
3 ... lemons

some php script (accessed parallely by multiple users at the time):

save_next_fruit($_GET['fruit']); 

will create some record in another tables with values like this:

saved_fruit:
ID | FRUIT(FK) | FRUIT_NO
1      1            1
2      1            2      
3      2            1
4      3            1
5      3            2
6      1            3
7      3            3
8      2            2
9      1            4
10     2            3
11     1            5
12     2            4
13     1            6
14     3            4
15     3            5

other words, I need to do this (e.g. for fruit 3 (lemons)):

insert into saved_fruit (fruit, fruit_no) values (3, select MAX(fruit_no)+1 from saved_fruit where fruit = 3);

but in thread safe way (I understand that above command is not thread safe in MyISAM MySQL database)

Can you help?

Thanks

图片转代码服务由CSDN问答提供 功能建议

我有一些记录的mysql表,例如:

   CREATE TABLE测试(
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 value varchar NOT NULL 
)
   
 
 

现在,我需要的是生成唯一序列 在PHP脚本中的1,2,...,N存储到另一个表...如何实现这一点是线程安全的,而不是创建双打或跳过某些东西?

我 我想知道是否有一些额外的mysql表可能会有所帮助,但我不知道如何创建类似“每个列值的单独自动增量”或其他任何东西......

  测试:
1 ... apples 
2 ... oranges 
3 ... lemons 
   
 
 

一些php脚本(当时多个用户并行访问) :

 <代码> save_next_fruit($ _ GET [ '水果']);  
   
 
 

将在另一个表中创建一些记录,其值如下所示:

  saved_fruit:
ID | 水果(FK)|  FRUIT_NO 
1 1 1 
2 1 2 
3 2 1 
4 3 1 
5 3 2 
6 1 3 
7 3 3 
8 2 2 
9 1 4 
10 2 3 
11 1 5 
12 2 4 \  n13 1 6 
14 3 4 
15 3 5 
   
 
 

换句话说,我需要这样做(例如水果3(柠檬)):

 插入saved_fruit(fruit,fruit_no)值(3,从saved_fruit中选择MAX(fruit_no)+1,其中fruit = 3); 
   
 \  n 

但是以线程安全的方式(我知道上面的命令在MyISAM MySQL数据库中不是线程安全的)

你能帮帮忙吗?

谢谢

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • drpjdfj618393 2013-11-14 00:19
    已采纳

    MyISAM does support this behavior. Create a two-column primary key, and make the second column auto-increment. It'll start over for each distinct value in the first column.

    CREATE TABLE t (i INT, j INT AUTO_INCREMENT, PRIMARY KEY (i,j)) ENGINE=MyISAM;
    INSERT INTO t (i) VALUES (1), (1), (2), (2), (1), (3);
    SELECT * FROM t; 
    
    +---+---+
    | i | j |
    +---+---+
    | 1 | 1 |
    | 1 | 2 |
    | 1 | 3 |
    | 2 | 1 |
    | 2 | 2 |
    | 3 | 1 |
    +---+---+
    

    But if you think about it, this is only thread-safe in a storage engine that does table-level locking for INSERT statements. Because the INSERT has to search other rows in the table to find the max j value per the same i value. If other people are doing INSERTs concurrently, it creates a race condition.

    Thus, the dependency on MyISAM, which does table-level locking on INSERT.

    See this reference in the manual: http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html under the section, MyISAM Notes.

    There are a whole lot of good reasons not to use MyISAM. The deciding factor for me is MyISAM's tendency to corrupt data.


    Re your comment:

    InnoDB does not support the increment-per-group behavior described above. You can make a multi-column primary key, but the error you got is because InnoDB requires that the auto-increment column be the first column in a key of the table (it doesn't strictly have to be the primary key)

    Regardless of the position of the auto-increment column in the multi-column key, it only increments when you use it with InnoDB; it does not number entries per distinct value in another column.

    To do this with an InnoDB table, you'd have to lock the table explicitly for the duration of the INSERT, to avoid race conditions. You'd do your own SELECT query for the max value in the group you're inserting to. Then insert that value + 1.

    Basically, you have to bypass the auto-increment feature and specify values instead of having them automatically generated.

    点赞 打赏 评论
  • dou5454954610 2013-11-14 00:19

    As you using MyISAM you could to lock whole table.

    LOCK TABLES `saved_fruit`;
    
    -- Insert query with select.
    
    UNLOCK TABLES;
    
    点赞 打赏 评论

相关推荐 更多相似问题