Geek Answers Handbook

MySQL INSERT or SELECT

I have 2 tables, table_a and table_b.

CREATE TABLE `table_a` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `val` varchar(64) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `val` (`val`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `table_b` (
 `ref_a` int(11) unsigned NOT NULL,
 `data` int(11) unsigned NOT NULL,
 UNIQUE KEY `unique_index` (`ref_a`,`data`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I want the MASS INSERT in table B with the value ref_a to refer to the ID of table A.

This is what I am trying to accomplish:

SELECT ID FROM table_a WHERE val = "123"

If the value does not exist, insert the value

INSERT INTO table_a(val) VALUES("123")

Now that I have the identifier (suppose it is "1"), I want to insert in table_b:

INSERT INTO table_b(ref_a, data) VALUES(1, 75)

The problem arises when I want to do it in bulk. Will my performance decrease if I alternate between SELECTS and INSERTS instead of doing a volume insert and then select a volume?

I could do:

INSERT INTO table_b(ref_a, data) VALUES((SELECT ID FROM table_a WHERE value="123"), 75)

but, what if the value is missing and I need to do the insert earlier.

I cannot START TRANSACTION and COMMIT, giving me the opportunity to get the identifier of table A after insertion.

I can also do:

  • Bulk insert into table A.
  • A.
  • B .

.

, ?

+5
2

, UNIQUE INDEX on value _a, INSERT IGNORE

INSERT IGNORE INTO table_a(value) VALUES("123"), ("345"), ("afasd"), ("#$#$%"), ...

, _a, table_b.

+3

, :

-

CREATE PROCEDURE insertdata(IN i_value VARCHAR(32), IN i_data INT(11) )
BEGIN
DECLARE v_insertID INT DEFAULT -1;

SELECT ID INTO v_insertID  FROM table_a WHERE value = i_value;

IF -1 = v_insertID
THEN
   INSERT INTO table_a(value) VALUES(i_value);
   SELECT ID INTO v_insertID  FROM table_a WHERE value = i_value;
END IF;

IF -1 != v_insertID
THEN
   INSERT INTO table_b(ref_a, data) VALUES(v_insertID, i_data);
END IF;

END

, , , create, _b ( )

, php . , php ( mysql)

+1

More articles:


All Articles