MySQL Insert into multiple tables? (Database normalization?)

What would happen, if you want to create many such records ones (to register 10 users, not just one)? I find the following solution (just 5 queryes):

Step I: Create temporary table to store new data.

CREATE TEMPORARY TABLE tmp (id bigint(20) NOT NULL, ...)...;

Next, fill this table with values.

INSERT INTO tmp (username, password, bio, homepage) VALUES $ALL_VAL

Here, instead of $ALL_VAL you place list of values: ('test1','test1','bio1','home1'),...,('testn','testn','bion','homen')

Step II: Send data to 'user' table.

INSERT IGNORE INTO users (username, password)
SELECT username, password FROM tmp;

Here, "IGNORE" can be used, if you allow some users already to be inside. Optionaly you can use UPDATE similar to step III, before this step, to find whom users are already inside (and mark them in tmp table). Here we suppouse, that username is declared as PRIMARY in users table.

Step III: Apply update to read all users id from users to tmp table. THIS IS ESSENTIAL STEP.

UPDATE tmp JOIN users ON tmp.username=users.username SET tmp.id=users.id

Step IV: Create another table, useing read id for users

INSERT INTO profiles (userid, bio, homepage) 
SELECT id, bio, homepage FROM tmp

No, you can't insert into multiple tables in one MySQL command. You can however use transactions.

BEGIN;
INSERT INTO users (username, password)
  VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage) 
  VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;

Have a look at LAST_INSERT_ID() to reuse autoincrement values.

You said "After all this time trying to figure it out, it still doesn't work. Can't I simply put the just generated ID in a $var and put that $var in all the MySQL commands?"

Let me elaborate: there are 3 possible ways here:

  1. In the code you see above. This does it all in MySQL, and the LAST_INSERT_ID() in the second statement will automatically be the value of the autoincrement-column that was inserted in the first statement.

    Unfortunately, when the second statement itself inserts rows in a table with an auto-increment column, the LAST_INSERT_ID() will be updated to that of table 2, and not table 1. If you still need that of table 1 afterwards, we will have to store it in a variable. This leads us to ways 2 and 3:

  2. Will stock the LAST_INSERT_ID() in a MySQL variable:

    INSERT ...
SELECT LAST_INSERT_ID() INTO @mysql_variable_here;
INSERT INTO table2 (@mysql_variable_here, ...);
INSERT INTO table3 (@mysql_variable_here, ...);

  3. Will stock the LAST_INSERT_ID() in a php variable (or any language that can connect to a database, of your choice):

    • INSERT ...
    • Use your language to retrieve the LAST_INSERT_ID(), either by executing that literal statement in MySQL, or using for example php's mysql_insert_id() which does that for you
    • INSERT [use your php variable here]

WARNING

Whatever way of solving this you choose, you must decide what should happen should the execution be interrupted between queries (for example, your database-server crashes). If you can live with "some have finished, others not", don't read on.

If however, you decide "either all queries finish, or none finish - I do not want rows in some tables but no matching rows in others, I always want my database tables to be consistent", you need to wrap all statements in a transaction. That's why I used the BEGIN and COMMIT here.


fairly simple if you use stored procedures:

call insert_user_and_profile('f00','http://www.f00.com');

full script:

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null
)
engine=innodb;

drop table if exists user_profile;
create table user_profile
(
profile_id int unsigned not null auto_increment primary key,
user_id int unsigned not null,
homepage varchar(255) not null,
key (user_id)
)
engine=innodb;

drop procedure if exists insert_user_and_profile;

delimiter #

create procedure insert_user_and_profile
(
in p_username varchar(32),
in p_homepage varchar(255)
)
begin
declare v_user_id int unsigned default 0;

insert into users (username) values (p_username);
set v_user_id = last_insert_id(); -- save the newly created user_id

insert into user_profile (user_id, homepage) values (v_user_id, p_homepage);

end#

delimiter ;

call insert_user_and_profile('f00','http://www.f00.com');

select * from users;
select * from user_profile;

Tags:

Mysql

Recent Posts