3

I was wondering how to make MySQL automatically have a column that adds 1 to every row that is made (Row 1 will have ID 1, Row 2 will get ID 2, etc.) For example:
Every time a new user signs up on a website, they are assigned an ID number. Starting at 1, then 2, etc.

ID|Username|Password
1 |Bob |drowssaP
2 |Jill |cats

Edward Nevard
  • 221
  • 2
  • 7
  • 19

1 Answers1

4

Try AUTO_INCREMENT. Documentation here

If you add, that magic word :) to your table creation declaration, it will do the magic for you :)

mysql> CREATE TABLE Users (
    -> ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Username varchar(255) NOT NULL,
    -> Password varchar(255) NOT NULL
    -> );
    Query OK, 0 rows affected (0.52 sec)

mysql> INSERT INTO Users(Username, Password) VALUES('vladimir', 'ilich_lenin');
    Query OK, 1 row affected (0.12 sec)

mysql> INSERT INTO Users(Username, Password) VALUES('friedrich', 'engels');
    Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM Users;
+----+-----------+-------------+
| ID | Username  | Password    |
+----+-----------+-------------+
|  1 | vladimir  | ilich_lenin |
|  2 | friedrich | engels      |
+----+-----------+-------------+
2 rows in set (0.02 sec)

EDIT

mysql> CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, Username varchar(255) NOT NULL, Password varchar(255) NOT NULL);
Query OK, 0 rows affected (0.25 sec)

mysql> SHOW CREATE TABLE 

  CREATE TABLE `Persons` (
  `ID` int(11) NOT NULL,
  `Username` varchar(255) NOT NULL,
  `Password` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)

mysql> ALTER TABLE Persons MODIFY ID INTEGER NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE Persons;
CREATE TABLE `Persons` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(255) NOT NULL,
  `Password` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)

mysql> INSERT INTO Persons(Username, Password) VALUES('friedrich', 'engels');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO Persons(Username, Password) VALUES('karl', 'marx');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM Persons
    -> ;
+----+-----------+----------+
| ID | Username  | Password |
+----+-----------+----------+
|  1 | friedrich | engels   |
|  2 | karl      | marx     |
+----+-----------+----------+
2 rows in set (0.00 sec)
marmeladze
  • 6,468
  • 3
  • 24
  • 45
  • Thanks, I actually already have an existing table called "requests" and it's got a lot more rows and I already have an ID column which I have assigned the Index role. Do you have any suggestions? – Edward Nevard Feb 10 '16 at 22:00
  • show your database structure if not so secret. Just execute `SHOW CREATE TABLE yourtablename;` and paste results here. – marmeladze Feb 10 '16 at 22:09
  • It contains a lot of users passwords as MD5's so I don't want that posted here, however I will tell you the structure. ID|Song|Name|Dedicated|Time Obviously, I want the ID column to give ID's automatically – Edward Nevard Feb 10 '16 at 22:14
  • `SHOW CREATE TABLE table_name` will only show the structure, not contents. I'm adding some lines to my answer. Wait a second. – marmeladze Feb 10 '16 at 22:22
  • https://i.gyazo.com/1eaecc0dcbf82bc9b7266efa6c40961e.png Here is my structure pulled from PHPMYADMIN – Edward Nevard Feb 10 '16 at 22:27
  • btw, that thread would be useful: http://stackoverflow.com/questions/5035836/how-to-add-auto-increment-to-an-existing-column – marmeladze Feb 10 '16 at 22:28
  • 1
    Indeed it was! ALTER TABLE requests MODIFY ID INTEGER NOT NULL AUTO_INCREMENT; Did the job! – Edward Nevard Feb 10 '16 at 22:31