Mysql自动递增类似数据的int [英] Mysql Auto-incrementing int of similar data

问题描述

所以这更多的是关于如何做到这一点的查询，我不熟悉MySQL / PHP编码，而不仅仅是基础知识，所以我只是想知道如何设置自动递增int如果两个姓氏相同则会计算它们。
我在网上搜索时无法找到任何内容，但一个例子是：

So this is more of a query of how one might go about doing this, I'm new to MySQL/PHP coding when it comes to more than the basics so I'm just wondering how one might set up an auto incrementing int where if two lastnames were the same it would count them. I was unable to find anything on it while searching online but an example would be:

在数据库中我们有5个用户

in the database we have 5 users

 1. james smith 1   
 2. terry smith 2
 3. john smith 3
 4. jerry fields 1
 5. tom straus 1

当这些用户注册时，我需要创建一个用于john的int史密斯是第三个拥有相同姓氏史密斯的人，而杰里菲尔德是第一个拥有姓氏字段等的人。怎么可能这样做？

When these users register I need an int to be created that john smith was the 3rd person to have the same last name of smith while jerry fields is the first person with the last name fields etc. How might one do that?

表格我做的是使用jquery / php ajax方法注册用户，但
我想添加类似于此的内容，以便将该数字与其名称组合以生成特定的用户ID。

The form I made is one that registers a user using a jquery/php ajax method but I would like to add something similar to this so that it combines that number with their names to make a specific user ID.

推荐答案

如使用 AUTO_INCREMENT ：

For MYI SAM 和 BDB 表，您可以在多列的辅助列上指定 AUTO_INCREMENT 指数。在这种情况下， AUTO_INCREMENT 列的生成值计算为 MAX（ auto_increment_column ）+ 1 WHERE prefix = 给定前缀 。当您想要将数据放入有序组时，这非常有用。

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 my_table (
  firstname VARCHAR(31) NOT NULL,
  lastname  VARCHAR(31) NOT NULL,
  counter   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (lastname, counter)
) Engine=MyISAM;

INSERT INTO my_table
  (firstname, lastname)
VALUES
  ('james', 'smith' ),
  ('terry', 'smith' ),
  ('john' , 'smith' ),
  ('jerry', 'fields'),
  ('tom'  , 'straus')
;

在 sqlfiddle 。

这篇关于Mysql自动递增类似数据的int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！