No Name Sex
1 A M
2 B F
3 C F
4 D M
I want to get automatic number on table with mysql database. So how to do that?
-
1It's not clear what you're after - could you expand your question a bit more and help clarify what you're getting currently, and what you'd really like?Gagravarr– Gagravarr2012-12-26 04:08:14 +00:00Commented Dec 26, 2012 at 4:08
-
1"automatic number on table with mysql database" what is that ??swapnesh– swapnesh2012-12-26 04:08:19 +00:00Commented Dec 26, 2012 at 4:08
-
Did you mean you want to get number in "no" field?gamehelp16– gamehelp162012-12-26 04:10:01 +00:00Commented Dec 26, 2012 at 4:10
-
Are you asking about the Auto Increment valuesenK– senK2012-12-26 04:10:32 +00:00Commented Dec 26, 2012 at 4:10
-
Ex. row 1 2 3 4 5 6 on table auto number, with sql row numberKim Jose– Kim Jose2012-12-26 04:13:06 +00:00Commented Dec 26, 2012 at 4:13
|
Show 2 more comments
4 Answers
You have create table with primary key and auto increment
http://dev.mysql.com/doc/refman/5.0/en///example-auto-increment.html
See in below code:
CREATE TABLE Persons
(
id int NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
sex char(1),
PRIMARY KEY (id)
)
There are various ways to do that,
One technique is to alter the table
- Alter table
- Add new column
- set it as
AUTO_INCREMENT
And the other is to use session variable
SELECT @rank := @rank+1 As `No`,
Name,
Sex
FROM table1, (SELECT @rank := 0) r
Comments
Automatic numbering is done by specifying the auto_increment attribute for the numeric column you wish to automatically increment. It is good database practice to specify it on an 'id' column when creating a table, with each id uniquely identifying a row (also known as a primary key).
CREATE TABLE people (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
sex ENUM('m', 'f'),
PRIMARY KEY (id)
) ENGINE=MyISAM;
Alternatively, you can add the ID (auto increment) after the table exists.
Comments
If you want to create a table that will do that automatically, roughly you can use...
create table mytable (
`No` serial,
/* other fields... */);
http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
However, if you delete rows you will have gaps in the row numbers. If you want to reset the auto_increment field, use
alter table mytable
auto_increment = 1;
