Mysql insert into 2 tables

I want to make a insert into 2 tables

visits:

visit_id int | card_id int

registration:

registration_id int | type enum('in','out') | timestamp int | visit_id  int

I want something like:

INSERT INTO `visits` as v ,`registration` as v
(v.`visit_id`,v.`card_id`,r.`registration_id`, r.`type`, r.`timestamp`, r开发者_运维知识库.`visit_id`) 
VALUES (NULL, 12131141,NULL, UNIX_TIMESTAMP(), v.`visit_id`);

I wonder if its possible

It's not possible with one query as INSERT can only insert data to one table in mysql. You can either

1. write this as two queries and execute them as a batch
2. create a stored procedure that would execute two insert command

You can wrap those inserts in transaction if you need to make sure that both queries will write the data.

It seems like the problem you are trying to solve is to get the auto-increment value from the "visits" row to insert into "registration". Am I right?

If so, you can just use the LAST_INSERT_ID() function like this:

INSERT INTO `visits` (`visit_id`,`card_id`) 
VALUES (NULL, 12131141);
INSERT INTO `registration` (`registration_id`, `type`, `timestamp`, `visit_id`) 
VALUES (NULL, 'in', UNIX_TIMESTAMP(), LAST_INSERT_ID());

You can always do something like this

INSERT IGNORE INTO `table2` VALUES ((select id from table1 where col="value"), 3, 4, 5)

INSERT INTO designation as de,
                department as da,
                    profile as pr

                                  (designation_name,
                                   depart_id,
                                   id,
                                   username,
                                   department,
                                   designation)


      select de.designation_name,
             de.depart_id,da.id,
             pr.username,
             pr.department,
             pr.designation 
       from 
              designation,
              department,
              profile

          de.designation_name='project manager' AND de.id='1'         OR
          de.depart_id='2' AND de.id='2'                              OR  
          da.id='2'                                                   OR  
          pr.username='kapil.purohit' AND pr.id='9' AND pr.status='1' OR 
          pr.department='1' AND pr.id='9'                             OR  
          pr.designation='3' AND pr.id='9' AND pr.status='1'

       WHERE               
               de.id    =   da.id   AND
                   da.id    =   pr.id   AND
                   de.id    =   pr.id   AND
                                    ORDER BY de.id DESC