MySQL safe operations

addcol.sql

DROP PROCEDURE IF EXISTS addcol;
delimiter '//' 
CREATE PROCEDURE addcol() BEGIN
	IF NOT EXISTS(
		(
			SELECT * FROM information_schema.COLUMNS 
			WHERE 		TABLE_SCHEMA=DATABASE()
					AND TABLE_NAME='my_table'
					AND COLUMN_NAME='value4'
		)
	) THEN
		ALTER TABLE my_table ADD `value4` INT NOT NULL DEFAULT 1 AFTER `value3`;
	END IF;
END;
//
delimiter ';'
CALL addcol();
DROP PROCEDURE IF EXISTS addcol;

altercol.sql

DROP PROCEDURE IF EXISTS altercol;
delimiter '//' 
CREATE PROCEDURE altercol() BEGIN
	IF EXISTS(
		(
			SELECT * FROM information_schema.COLUMNS 
			WHERE 		TABLE_SCHEMA=DATABASE()
					AND TABLE_NAME='my_table'
					AND COLUMN_NAME='value4'
		)
	) THEN
		ALTER TABLE `my_table` MODIFY `value4` VARCHAR(255) DEFAULT NULL;
	END IF;
END;
//
delimiter ';'
CALL altercol();
DROP PROCEDURE IF EXISTS altercol;

create.sql

CREATE TABLE IF NOT EXISTS `my_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `value1` VARCHAR(255) DEFAULT NULL,
  `value2` VARCHAR(255) DEFAULT NULL,
  `value3` VARCHAR(255) DEFAULT NULL,
  KEY `id` (`id`)
);

doubleinsert.sql

/* Insert in first table */
INSERT INTO `my_table` VALUES (NULL, 'My first line', 'and second col', 'then first value3', 'finally, last value');
 
/* Retrieve the ID of the newly created line */
SELECT @MAX:=MAX(id) FROM `my_table`;
 
/* Insert a new line with this */
INSERT INTO `my_second_table` (`id`, `my_table_id`, `value1`) VALUES (NULL, @MAX, 'My first line on second table !');

insertcheck.sql

INSERT INTO `my_table`(`value1`,`value2`,`value3`,`value4`)
SELECT * FROM (
    SELECT 'My first line', 'and second col', 'then first value3', 'finally, last value') AS tmp
WHERE NOT EXISTS (
    SELECT `value1`
    FROM `my_table`
    WHERE `value1` = 'My first line'
) LIMIT 1;

insertdoublevalue.sql

INSERT INTO `my_table`(`value1`,`value2`,`value3`,`value4`)
SELECT * FROM (
    SELECT 'My first line' as col1, 'My first line' as col2, 'My first line' as col3, 'My first line' as col4) AS tmp
WHERE NOT EXISTS (
    SELECT `value1`
    FROM `my_table`
    WHERE `value1` = 'My first line'
) LIMIT 1;

insertidexisting.sql

SELECT @line:=`id` FROM `my_table` WHERE (value1 = 'My first line');
 
INSERT INTO `my_second_table` (`my_table_id`, `value1`)
SELECT * FROM (
    SELECT @line, 'My first line on second table !') AS tmp
	WHERE NOT EXISTS (
        SELECT `value1`
        FROM `my_second_table`
        WHERE  (`value1` = 'My first line on second table !')
    )
LIMIT 1;