JIYIK CN >

This article will introduce transactions and stored procedures for inserting into multiple tables in MySQL.

Inserting into multiple tables in MySQL

It is not possible to insert into multiple tables with a single MySQL command, but we can use MySQL transactions to meet the project requirements.

Let us create two tables named users and user_profiles. The users table has three attributes, user_id, user_name, and user_password, while the profiles table contains user_id, user_bio, and homepage as attributes.

See the following commands we used to create these two tables.

Sample code:

CREATE TABLE users(
    user_id INT NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(45) NOT NULL,
    user_password VARCHAR(45) NOT NULL,
    PRIMARY KEY(user_id)
);

CREATE TABLE user_profiles(
    user_id VARCHAR(45) NOT NULL,
    user_bio VARCHAR(45) NOT NULL,
    homepage VARCHAR(50) NOT NULL
);

Here, we have created two tables. Now, we can insert data into both tables at the same time in the following way.

Remember that the values ​​of user_profiles.user_idand users.user_idare the same.

Sample code:

BEGIN;
    INSERT INTO users (user_id,user_name, user_password)
    VALUES (2,'username2', 'userpassword2');
    SELECT @UserID := MAX(user_id) FROM users;
    INSERT INTO user_profiles (user_id, user_bio, homepage)
    VALUES(@UserID,'this is bio for username2', 'http://www.username2.com');
COMMIT;

Add two records and use the SELECT statement to view the results.

Output ( users table):

+---------+-----------+---------------+
| user_id | user_name | user_password |
+---------+-----------+---------------+
|       1 | username1 | userpassword1 |
|       2 | username2 | userpassword2 |
+---------+-----------+---------------+
2 rows in set (0.03 sec)

Output ( user_profiles table):

+---------+---------------------------+--------------------------+
| user_id | user_bio                  | homepage                 |
+---------+---------------------------+--------------------------+
| 1       | this is bio for username1 | http://www.username1.com |
| 2       | this is bio for username2 | http://www.username2.com |
+---------+---------------------------+--------------------------+
2 rows in set (0.00 sec)

Alternatively, we can create a stored procedure as follows to save time and effort.

Sample code:

DELIMITER ;;

CREATE PROCEDURE InsertALL()
BEGIN
    INSERT INTO users (user_id,user_name, user_password)
    VALUES (3,'username3', 'userpassword3');
    SELECT @UserID := MAX(user_id) FROM users;
    INSERT INTO user_profiles (user_id, user_bio, homepage)
    VALUES(@UserID,'this is bio for username3', 'http://www.username3.com');
END ;;

DELIMITER ;