mysql自动增长int_Mysql自动递增类似数据的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:

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

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?

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.

解决方案

As documented under Using AUTO_INCREMENT:

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.

Therefore, you could do:

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

;

See it on sqlfiddle.