Is InnoDB neccessary for this case?

Question

I have been looking for the answer to the question below:

MySQL: Insert record if not exists in table

I have an extra question to the accepted answer. However, I don't have enough reputation to comment on the page...

In the answer, Mike create the table like this:

CREATE TABLE `table_listnames` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `tele` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

Is InnoDB a must, according to the query issued below? (also quoted from the answer)

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'John', 'Doe', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'John'
) LIMIT 1;

Thanks.

No, InnoDB is not required. But it is now the default. MyISAM does not support ACID. — Boris the Spider, Dec 08 '15 at 14:56
Thank you! I like StackOverflow - members like you are always eager to help and I have my answer within 5 minutes! — Chung Lun Yuen, Dec 08 '15 at 14:59

score 1 · Accepted Answer · answered Dec 08 '15 at 15:04

1

No, it is not necessary.

But I prefer to write your query this way:

INSERT INTO table_listnames (name, address, tele)
SELECT 'John', 'Doe', '022' FROM dual
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'John'
);

or even better, set an unique constraint:

ALTER TABLE table_listnames ADD UNIQUE (name)

and just insert with this:

INSERT INTO table_listnames (name, address, tele)
VALUES ('John', 'Doe', '022')

if a row with the name John is already present, the INSERT will simply fail.

answered Dec 08 '15 at 15:04

fthiella

44,757
15
80
100

Oh thanks for your suggestion! The variable concerned is already `UNIQUE`. I just want to know if there's duplicate. If it is so, I have to get the key of that row... and talking about detecting duplicates, after some more search, I find out that `on duplicate update keys` does both jobs in mysql... is that right? – Chung Lun Yuen Dec 08 '15 at 17:25
@ChungLunYuen yes, if you want to INSERT a new record when the name is not present, or UPDATE an existing one you have to set a UNIQUE constraint and use INSERT ... ON DUPLICATE ... see here http://stackoverflow.com/questions/14383503/on-duplicate-key-update-same-as-insert – fthiella Dec 08 '15 at 17:28

Is InnoDB neccessary for this case?

1 Answers1