Mysql: Auto increment in phpmyadmin

I have an existing database using PHP, MySQL and phpMyAdmin.

When users become a member on my website, I need the system to create a unique membership number for them using a five digit number. for e.g 83773. I guess it is like generating a random password except I only want numbers for my members. This ID number has to be unique to each member.

Would it be possible for me to set the primary key to auto_increment in my user table and set it to start at 10000 and then auto increment every time a member registers?

Also, is there a maximum number that the primary key ID number would go up to?

Is this a reliable and safe way to use the primary key ID number as a membership number?

 Answers

There is steps to make auto incriment for a column. I guess the phpMyAdmin version is 3.5.5 but not sure.

Click on Table > Structure tab > Under Action click on Change on the ppoup window scroll left and check A_I. Also make sure you have selected Nonefor Default

---

Just run a simple MySQL query and set the auto increment number to whatever you want.

ALTER TABLE `table_name` AUTO_INCREMENT=10000

In terms of a maximum, as far as I am aware there is not one, nor is there any way to limit such number.

It is perfectly safe, and common practice to set an id number as a primiary key, auto incrementing int. There are alternatives such as using PHP to generate membership numbers for you in a specific format and then checking the number does not exist prior to inserting, however for me personally I'd go with the primary id auto_inc value.

---

In phpMyAdmin, if you set up a field in your table to auto increment, and then insert a row and set that field's value to 10000, it will continue from there.

---

You cannot set a maximum value (other than choosing a datatype which cannot hold large numbers, but there are none that have the limit you're asking for). You can check that with LAST_INSERT_ID() after inserting to get the id of the newly created member, and if it is too big handle it in your application code (e.g., delete and reject the member).

Why do you want an upper limit?

How to force MySQL to take 0 as a valid auto-increment value

Long story short, I have a SQL file that I want to import as a skel style file, so this will be done repeatedly, programmatically. I can edit the SQL file however I want, but I'd rather not touch the application itself.

This application uses userid = 0 to represent the anonymous user. It also has a relevant (blank) entry in the database to represent this 'user'. Hence, the line in my skel.sql looks something like this:

INSERT INTO `{{TABLE_PREFIX}}users` VALUES (0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, '', '', '', NULL);

The problem with this is that uid is a auto_increment field, for which, technically, 0 is an invalid value. Or atleast, if you set it to 0, you're basically telling MySQL, "Please insert the next id into this field."

Now, I suppose I could put an INSERT then an UPDATE query into my SQL file, but is there a way of telling MySQL in general that yes, I actually want to insert 0 into this field?

 Answers

From the answer I got here:

You can use:

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

Which as described here, will prevent MySQL from interpreting an INSERT/UPDATE ID of 0 as being the next sequence ID. Such behaviour will be limited to NULL.

It is what I'd consider pretty bad behaviour from the application though. You'll have to be real careful that it's used consistently, especially if you choose to implement replication at a later date.

You can use:

SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

Which as described here, will prevent MySQL from interpreting an INSERT/UPDATE ID of 0 as being the next sequence ID. Such behaviour will be limited to NULL.

It is what I'd consider pretty bad behaviour from the application though. You'll have to be real careful that it's used consistently, especially if you choose to implement replication at a later date.

---

If you do not want to mangle with mysql variables, here's a useful hack:

INSERT INTO `{{TABLE_PREFIX}}users` VALUES (-1, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, 0, NULL, '', '', '', NULL);

And then

UPDATE `{{TABLE_PREFIX}}users` SET id = 0 where id = -1;

Obviously, this assumes that you're not using negative values for your table ids.

---

How do I insert the key 0 into mysql for AUTO_INCREMENT row?

From http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

"No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers."

So it seems using 0 just tells it to auto_increment.

But if you are assigning ID numbers, I'm not sure why you have defined the column to use AUTO_INCREMENT?

---

You cannot force this to be zero. This is limited by auto_increment_offset server variable. The default value is 1, the range is [1 .. 65535].

More information: auto_increment_offset, How to set AUTO_INCREMENT step.

---

The basic solution is this:

Blockquote SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

See my comment for an additional issue I solved too with this being overridden mid script.

Mysql: How to set AUTO_INCREMENT step

How to set AUTO_INCREMENT step

The AUTO_INCREMENT attribute is used to generate a unique value for an identity column.

Let us check how it works:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

<pre><code>-- Create table CREATE TABLE employees(   id INT(11) AUTO_INCREMENT,   name VARCHAR(30),   PRIMARY KEY (id) ); -- Add some records INSERT INTO employees (name)   VALUES ('John'), ('Angela'), ('George'), ('Kate'); -- Retrieve data SELECT id, name FROM employees; +----+--------+ | id | name   | +----+--------+ |  1 | John   | |  2 | Angela | |  3 | George | |  4 | Kate   | +----+--------+

As you can see id values were generated from 1 to 4 with step 1. To control AUTO_INCREMENT operation we can use server variables – auto_increment_increment  And auto_increment_offset.

• auto_increment_increment – is the incremental value, controls the interval between successive column values.
• auto_increment_offset – determines the starting value for the AUTO_INCREMENT field; this value is used for the first record inserted into the table.

These variables have session and global scope.

Let us try to use these variables –

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

<pre><code>-- Empty table TRUNCATE TABLE employees; -- Set variables (SESSION scope) SET @@session.auto_increment_increment = 10; SET @@session.auto_increment_offset = 5; -- Add some records INSERT INTO employees (name)   VALUES ('John'), ('Angela'), ('George'), ('Kate'); -- Retrieve data SELECT id, name FROM employees; +----+--------+ | id | name   | +----+--------+ |  5 | John   | | 15 | Angela | | 25 | George | | 35 | Kate   | +----+--------+

The first records has id = 5, next id is 15 and so on.