Top 5 Advanced MySQL Features, Part 1

Stored Procedures

Stored procedures are pre-compiled, reusable code blocks that can be executed with a single call, making it easier to perform complex or repetitive tasks. They also improve security by allowing you to control access to specific parts of the database.

Are you tired of using the same old MySQL features and ready to level up your database game? Look no further! In this series, we’re going to show you five advanced MySQL features that you should definitely add to your toolkit. Trust us, once you start using these, you’ll wonder how you ever managed without them. Whether you’re a pro at using MySQL or just getting started, these features will come in handy.

So grab a cup of coffee and let’s get started on discovering these hidden gems of MySQL, starting with stored procedures!

Some common use cases for stored procedures:

Data validation:

CREATE PROCEDURE insert_user(IN name VARCHAR(255), IN age INT)
BEGIN
  IF LENGTH(name) = 0 OR name IS NULL THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Name cannot be empty';
  END IF;

  IF age < 18 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Age must be at least 18';
  END IF;

  INSERT INTO users (name, age) VALUES (name, age);
END;

Data transformation:

CREATE PROCEDURE insert_user_details(IN name VARCHAR(255), IN email VARCHAR(255))
BEGIN
  DECLARE username VARCHAR(255);
  SET username = LOWER(name); 
  INSERT INTO users (username, email) VALUES (username, email);
END;

Data aggregation:

CREATE PROCEDURE total_sales_by_year(IN year INT)
BEGIN
  SELECT SUM(amount) AS total_sales, COUNT(*) AS number_of_sales
  FROM sales
  WHERE YEAR(date) = year;
END;

Data security:

CREATE PROCEDURE update_user_password(IN user_id INT, IN new_password VARCHAR(255))
BEGIN
  DECLARE current_password VARCHAR(255);

  SELECT password INTO current_password FROM users WHERE id = user_id;

  IF current_password = new_password THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'New password cannot be the same as the current password';
  END IF;

  SET @salt = UNHEX(SHA2(UUID(), 256));
  SET @password_hash = UNHEX(SHA2(CONCAT(@salt, new_password), 256));

  UPDATE users SET password_hash = @password_hash, salt = @salt WHERE id = user_id;
END;

DB Backup:

CREATE PROCEDURE backup_database()
BEGIN
  DECLARE backup_name VARCHAR(255);

  SET backup_name = CONCAT(DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'), '.sql');

  SELECT CONCAT('mysqldump -u', user(), '-p', password, ' ', 
                database(), ' > ', backup_name) INTO @backup_command;

  PREPARE stmt FROM @backup_command;
  EXECUTE stmt;

  DEALLOCATE PREPARE stmt;
END;

DB Recovery:

CREATE PROCEDURE recover_database(IN backup_file VARCHAR(255))
BEGIN
  SET FOREIGN_KEY_CHECKS = 0;

  SELECT CONCAT('mysql -u', user(), '-p', password, ' ', 
                database(), ' < ', backup_file) INTO @recovery_command;

  PREPARE stmt FROM @recovery_command;
  EXECUTE stmt;

  DEALLOCATE PREPARE stmt;

  SET FOREIGN_KEY_CHECKS = 1;
END;

Data reporting:

CREATE PROCEDURE generate_sales_report(IN start_date DATE, IN end_date DATE)
BEGIN
  SELECT product_name, SUM(quantity) as total_quantity, SUM(total_price) as total_revenue
  FROM sales
  WHERE sale_date BETWEEN start_date AND end_date
  GROUP BY product_name;
END;

Data analysis:

CREATE PROCEDURE analyze_customer_data(IN customer_id INT)
BEGIN
  SELECT 
    customer_id, 
    COUNT(DISTINCT order_id) as total_orders, 
    SUM(total_price) as total_spend, 
    AVG(total_price) as average_order_value, 
    MAX(total_price) as highest_order_value, 
    MIN(total_price) as lowest_order_value
  FROM orders
  WHERE customer_id = customer_id
  GROUP BY customer_id;
END;

Data migration:

CREATE PROCEDURE migrate_data(IN src_table VARCHAR(255), IN dest_table VARCHAR(255))
BEGIN
  INSERT INTO dest_table
  SELECT * FROM src_table;
END;