Mysql – Prepopulate database with empty / null values [thesql]

database-designMySQL

What would be the best way to pre-populate a database with 100 empty fields and then begin the Auto_Increment?

What I mean to say, I want to reserve the first 100 userfields (ie UIDs which are auto_increment) 0 – 100 for "special users" and have the AUTO_INCREMENT start at 101 (as an example).

Obviously I can't set Auto_Increment to 101 as then it will start the UID at 101. And, I don't know beforehand how many special users there will be, however 100 sounds like a reasonable number to reserve.

What kind of null data can I insert and how would you go about this?

Best Answer

Actually, yes you can start an empty table's auto_increment from 101.

SAMPLE CODE

use test
drop table if exists fizzydrink;
create table fizzydrink
(id int not null auto_increment,num1 int,num2 int,primary key (id));
show create table fizzydrink\G
alter table fizzydrink auto_increment = 101;
show create table fizzydrink\G
insert into fizzydrink (num1,num2) values (1,2),(3,4),(5,6);
select * from fizzydrink;

SAMPLE CODE EXECUTED

mysql> use test
Database changed
mysql> drop table if exists fizzydrink;
Query OK, 0 rows affected (0.02 sec)

mysql> create table fizzydrink
    -> (id int not null auto_increment,num1 int,num2 int,primary key (id));
Query OK, 0 rows affected (0.05 sec)

mysql> show create table fizzydrink\G
*************************** 1. row ***************************
       Table: fizzydrink
Create Table: CREATE TABLE `fizzydrink` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table fizzydrink auto_increment = 101;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table fizzydrink\G
*************************** 1. row ***************************
       Table: fizzydrink
Create Table: CREATE TABLE `fizzydrink` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num1` int(11) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into fizzydrink (num1,num2) values (1,2),(3,4),(5,6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from fizzydrink;
+-----+------+------+
| id  | num1 | num2 |
+-----+------+------+
| 101 |    1 |    2 |
| 102 |    3 |    4 |
| 103 |    5 |    6 |
+-----+------+------+
3 rows in set (0.00 sec)

mysql>

CREATE 100 INITIAL USERS

use test
drop table if exists fizzydrink;
create table fizzydrink
(id int not null auto_increment,num1 int,num2 int,primary key (id));
show create table fizzydrink\G
insert into fizzydrink (id) values
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
select * from fizzydrink;

You cannot add user id 0 because the auto_increment interprets it as GET NEXT ID

GIVE IT A TRY !!!

Related Question