Home > Database > Mysql Tutorial > body text

How to Implement Auto-Increment by Group in MySQL 5.0?

Mary-Kate Olsen
Release: 2024-10-31 23:20:28
Original
608 people have browsed it

How to Implement Auto-Increment by Group in MySQL 5.0?

Achieving Auto-Increment by Group in MySQL 5.0

The task of having an auto-increment field based on a grouping column in MySQL 5.0 arises when organizing data with unique identifiers within specific groups. To achieve this, leverage the following method specific to MyISAM and BDB table types.

Creating a Table with Auto-Increment Grouping

Create a table with an auto-increment field as a secondary part of the primary key. For example:

CREATE TABLE foo (
   id          INT AUTO_INCREMENT NOT NULL,
   group_field INT NOT NULL,
   name        VARCHAR(128),

   PRIMARY KEY(group_field, id)
);
Copy after login

Key Generation

The auto-increment field's value is calculated using the following formula:

MAX(auto_increment_column) 1 WHERE prefix=given-prefix

This ensures that records within each group are assigned sequential identifiers.

Reference

For further details, refer to the MySQL manual:


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.


The above is the detailed content of How to Implement Auto-Increment by Group in MySQL 5.0?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!