MYSQL: How can I create a new row and get the new id without inserting data?

Question

Normally I get a new ID by inserting some data and it creates a new row and returns the new ID. But if I dont want to insert any data I just want to create a new row with a new ID and get the new ID...how can I do that?

Thanks.

UPDATE:

OK Here is my issue. The table I wanted to do this to only has the 1 ID column. Why? I'll explain (we'll I'll try to). I have another table where each row has its own unique ID variation_id (auto-inc), but each row needs to be tied to a group of other rows from the same table. I have another column called group_id I can't have it auto-inc because it needs to appear multiple times, it his what says which variations should be grouped together. So I wanted to have a second table with group_id as the primary key and auto-inc so I could use that to generate the new group_id whenever I need a new group. I guess I am going about this the wrong way....so what should I do?

Answer 1

All your columns would have to be nullable, and you'd have to insert null for all the other columns but ID.

Answer 2

Need more detail to answer but, if all the columns in the table are nullable then you can just insert an empty row and get back the auto-generated ID.

Answer 3

Then need to have an table layout with accept null for each column and just insert null values.

If just need the id, lock the table, select max(id) from that table and add 1.Use the info and the consume the new row and release the lock.

Answer 4

Cant you just pass null values, or am I misunderstanding the question? The query would then become:

INSERT INTO tableName values (null)

If your tables dont accept null values they could be set up with a default value, which makes you able to do the following query:

INSERT INTO tableName VALUES (default)

Default is a keyword which explicitly specifies the default value for a column. MySQL allows you to specify an empty values list if all columns have a default value defined: insert into D values()

Edit: since the other answers arrived and none have mentioned the default keyword Id like to show how you create tables with default values. It goes like this:

CREATE TABLE tableName (id integer default 0, foo varchar(10))

Now if you want to you can insert the default value for ID by doing only:

INSERT INTO tableName (foo) values ('bar')

Answer 5

SHOW CREATE TABLE will show you the internal auto_increment value that would be assigned to the next inserted row. Note that you should not use that value in following statements, since the value might change through a different insert operation the microsecond you receive it. You can rather use LAST_INSERT_ID() in other queries if that suits your needs - LAST_INSERT_ID() will give you the last id that was inserted within this session (=connection), so it is safe to rely on that value.

mysql> CREATE TEMPORARY TABLE test (id INT PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TEMPORARY TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO test(id) VALUES(10);
Query OK, 1 row affected (0.00 sec)

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TEMPORARY TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 | 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Edit after clarification of question:

You should create tables that reflect that logic and give them proper names. Create a table called groups (or something better suitable depending on your application) and insert a new entry each time you want to generate a group. You can assign values to that group by using its LAST_INSERT_ID(). Precalculating auto_increment values is very dangerous and will eventually lead to duplicate group ids - which can be anything from extremely annoying to fatal - depending on what your application is doing.