Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SEQUENCE IN MYSQL.
- Thread starter 25884
- Start date
Well, there's no real trick to it. You just use AUTO_INCREMENT in your table creation statements like so:
CREATE TABLE users (
id int(11) auto_increment,
username text,
password text,
);
This creates a table with a column called 'id', which has an integer datatype (AUTO_INCREMENT needs that), and since the column is defined as auto_increment, this means that the sequence happens automatically. Every time you insert records into the table, if you insert nothing in the 'id' column, it will automatically place a number that is 1 value higher than the number in the previous record, starting with the number 1, if no number is specified for the first record. If you insert a number in that column, such as 1000, then the next record will automatically insert 1001, unless otherwise specified.
This is not a true SEQUENCE tool, such as that in PostgreSQL. It only handles numbers, and it only increments by 1, but without too much trouble, you can create other sequences based on auto_increment values, if needed.
The most common usage for auto_increment is to define a unique primary key for your table:
CREATE TABLE users (
id int(11) NOT NULL auto_increment,
username tinytext NOT NULL,
password tinytext NOT NULL,
PRIMARY KEY (id),
UNIQUE id (id),
KEY id_2 (id)
);
This table creation statement uses the 'id' column as a primary key, constrains it to unique values, and indexes the column for speed, so that SELECTs based on the user's ID happen quite fast.
CREATE TABLE users (
id int(11) auto_increment,
username text,
password text,
);
This creates a table with a column called 'id', which has an integer datatype (AUTO_INCREMENT needs that), and since the column is defined as auto_increment, this means that the sequence happens automatically. Every time you insert records into the table, if you insert nothing in the 'id' column, it will automatically place a number that is 1 value higher than the number in the previous record, starting with the number 1, if no number is specified for the first record. If you insert a number in that column, such as 1000, then the next record will automatically insert 1001, unless otherwise specified.
This is not a true SEQUENCE tool, such as that in PostgreSQL. It only handles numbers, and it only increments by 1, but without too much trouble, you can create other sequences based on auto_increment values, if needed.
The most common usage for auto_increment is to define a unique primary key for your table:
CREATE TABLE users (
id int(11) NOT NULL auto_increment,
username tinytext NOT NULL,
password tinytext NOT NULL,
PRIMARY KEY (id),
UNIQUE id (id),
KEY id_2 (id)
);
This table creation statement uses the 'id' column as a primary key, constrains it to unique values, and indexes the column for speed, so that SELECTs based on the user's ID happen quite fast.
- Status