It's easy to create a database table for storing sequence
numbers ; but this design is suited for the event when the sequence
is shared for all users. What I want is to create sequence
for each group of users : this group
can grow at any time because it's a database table , that is the administrator can create a group at any time and users are assigned to a specific group. So how to implement the sequence
generation according to a group ?
如何为每组用户实现序列号数据库?
- 写回答
- 好问题 0 提建议
- 追加酬金
- 关注问题
- 邀请回答
-
1条回答 默认 最新
- douchuituo3032 2012-07-13 08:12关注
if you are using myisam
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
Below extracted from above links.
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id) ) ENGINE=MyISAM; INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich'); SELECT * FROM animals ORDER BY grp,id; Which returns: +--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 1 | dog | | mammal | 2 | cat | | mammal | 3 | whale | | bird | 1 | penguin | | bird | 2 | ostrich | +--------+----+---------+
For your case:
CREATE TABLE mytable ( user_id MEDIUMINT NOT NULL AUTO_INCREMENT, group_id MEDIUMINT NOT NULL, user_name CHAR(30) NOT NULL, PRIMARY KEY (group_id,user_id) ) ENGINE=MyISAM; INSERT INTO mytable (group_id, user_name) VALUES (1,'alex'),(1,'jenny'),(2,'baz'),(1,'tim'),(2,'danny'),(3,'joe'); SELECT * FROM mytable ORDER BY group_id,user_id;
Returns:
user_id group_id user_name 1 1 alex 2 1 jenny 3 1 tim 1 2 baz 2 2 danny 1 3 joe
本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 无用评论 打赏 举报
悬赏问题
- ¥15 机器学习训练相关模型
- ¥15 Todesk 远程写代码 anaconda jupyter python3
- ¥15 我的R语言提示去除连锁不平衡时clump_data报错,图片以下所示,卡了好几天了,苦恼不知道如何解决,有人帮我看看怎么解决吗?
- ¥15 在获取boss直聘的聊天的时候只能获取到前40条聊天数据
- ¥20 关于URL获取的参数,无法执行二选一查询
- ¥15 液位控制,当液位超过高限时常开触点59闭合,直到液位低于低限时,断开
- ¥15 marlin编译错误,如何解决?
- ¥15 有偿四位数,节约算法和扫描算法
- ¥15 VUE项目怎么运行,系统打不开
- ¥50 pointpillars等目标检测算法怎么融合注意力机制