posts under mysql tag

Replace string in MySQL query

UPDATE `table` SET `row` = REPLACE(`row`, 'from_string', 'to_string');

MySQL triggers

Here are some examples of triggers creation syntax (MySQL user needs the SUPER privilege) :

DELIMITER //

CREATE TRIGGER `ai_trigger_name` AFTER INSERT ON `related_table_name`
  FOR EACH ROW BEGIN
    INSERT INTO some_table (row1, row2, row3) VALUES (NEW.row1, NEW.row2, NEW.row3);
    INSERT INTO another_table (row1, row2, row3) VALUES (NEW.row1, NEW.row2, NEW.row3);
  END;
//

CREATE TRIGGER `au_trigger_name` AFTER UPDATE ON `related_table_name`
  FOR EACH ROW UPDATE some_table SET row1 = NEW.row1, row2 = NEW.row2, row3 = NEW.row3 WHERE id = NEW.id;
//

CREATE TRIGGER `ad_trigger_name` AFTER DELETE ON `related_table_name`
  FOR EACH ROW DELETE FROM some_table WHERE row1 = OLD.row1;
//

DELIMITER ;

MySQL privileges snippet

To create a MySQL database with its own user :

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'user_password';

GRANT USAGE ON *.* TO 'user_name'@'localhost' IDENTIFIED BY 'user_password';

CREATE DATABASE IF NOT EXISTS `database_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

GRANT ALL PRIVILEGES ON `database_name`.* TO 'user_name'@'localhost';