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 关于#matlab#的问题:在模糊控制器中选出线路信息,在simulink中根据线路信息生成速度时间目标曲线(初速度为20m/s,15秒后减为0的速度时间图像)我想问线路信息是什么
- ¥15 banner广告展示设置多少时间不怎么会消耗用户价值
- ¥16 mybatis的代理对象无法通过@Autowired装填
- ¥15 可见光定位matlab仿真
- ¥15 arduino 四自由度机械臂
- ¥15 wordpress 产品图片 GIF 没法显示
- ¥15 求三国群英传pl国战时间的修改方法
- ¥15 matlab代码代写,需写出详细代码,代价私
- ¥15 ROS系统搭建请教(跨境电商用途)
- ¥15 AIC3204的示例代码有吗,想用AIC3204测量血氧,找不到相关的代码。