Insert query check if record exists - If not, Insert it

Question

I have a mysql table ip_list...

+----+---------------+
| id |    ip_addr    |
+----+---------------+
|  1 | 192.168.100.1 |
|  2 | 192.168.100.2 |
|  3 | 192.168.100.3 |
|  4 | 192.168.100.4 |
|  5 | 192.168.100.5 |
+----+---------------+

I want to add only that records which are not in ip_addr column. For ex

I have following records to add in ip_addr table

192.168.100.6
192.168.100.10
192.168.100.11
192.168.100.1   //already in column
192.168.100.12

I don't want to add 192.168.100.1, because it is already in column.

So, is it possible that INSERT query first check the records then insert it?

Currently, I am doing this with.. first I SELECT the records then match it and then INSERT it.

but, I want to do with only one query.

Answer 1

You can use below query. Here it will insert the ip_address when it is not present in your table.

INSERT INTO ip_list (ip_addr)
SELECT * FROM (SELECT '192.168.100.1') AS tmp
WHERE NOT EXISTS (
    SELECT ip_addr FROM ip_list WHERE ip_addr='192.168.100.1'
);