SQL - How to make a conditional INSERT

Question

Using only MySQL, I'm seeing if it's possible run an insert statement ONLY if the table is new. I successfully created a user variable to see if the table exists. The problem is that you can't use "WHERE" along with an insert statement. Any ideas on how to get this working?

// See if the "country" table exists -- saving the result to a variable
SELECT
    @table_exists := COUNT(*)
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'country';

// Create the table if it doesn't exist
CREATE TABLE IF NOT EXISTS country (
    id INT unsigned auto_increment primary key,
    name VARCHAR(64)
);

// Insert data into the table if @table_exists > 0
INSERT INTO country (name) VALUES ('Afghanistan'),('Aland Islands') WHERE 0 < @table_exists;

Answer 1

IF @TableExists > 0 THEN
   BEGIN
       INSERT INTO country (name) VALUES ('Afghanistan'),('Aland Islands');
   END

Answer 2

Use an if statement instead of the where clause:

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

Answer 3

Does that IF statement need to be within a function? Could someone show me a complete example?

Answer 4

I think it should be in a function or procedure, but I'm not sure it's the only way...

Here you have a procedure example:

DELIMITER //
DROP PROCEDURE IF EXISTS insertRegion //
CREATE PROCEDURE insertRegion(IN val_country_id VARCHAR(2), IN val_code VARCHAR(32), IN val_default_name VARCHAR(255))
    BEGIN
    DECLARE n TINYINT;
    SET n = (SELECT COUNT(country_id) FROM directory_country_region WHERE default_name = val_default_name);
    IF n = 0 THEN
        INSERT INTO directory_country_region (country_id, code, default_name) VALUES (val_country_id, val_code, val_default_name);
    END IF;
    END;
    //
DELIMITER ;

I hope it helps you!