# MySql Snippets

# Components in SQL

There are 5 components of the SQL language:

DDL: data definition language, such as CREATE, ALTER, DROP
DQL: data query language, such as SELECT
DML: data manipulation language, such as INSERT, UPDATE, DELETE
DCL: data control language, such as GRANT, REVOKE
TCL: transaction control language, such as COMMIT, ROLLBACK

# Clone table

CREATE TABLE foo SELECT * FROM bar LIMIT 0

# Move row from 1 table to another

Archived purpose - an an alternative way to soft delete. Avoid inserting duplication by unique key case.

INSERT INTO archived_posts 
    SELECT * FROM posts WHERE id = 1;

DELETE FROM posts WHERE id = 1;

# Sort by relevant (Fulltext search)

SELECT 
    SQL_CALC_FOUND_ROWS product_id, 
    MATCH ( text_data ) AGAINST ( '{$params['keyword']}' IN NATURAL LANGUAGE MODE ) as `score`
FROM {$table_name} 
WHERE MATCH ( text_data ) AGAINST ( '{$params['keyword']}' IN NATURAL LANGUAGE MODE ) > 0
ORDER BY `score` DESC

# Update Batch with specific condition

UPDATE people 
SET name = 
  (CASE id WHEN 1 THEN 'Karl'
           WHEN 2 THEN 'Tom'
           WHEN 3 THEN 'Mary'
   END)
WHERE id IN (1,2,3);

Note: This feature is not available in Laravel. This package will help you laravel-batch (opens new window)

# Ordering by specific field value

SELECT * 
FROM mytable WHERE id IN (1,2,3,4) 
ORDER BY FIELD(id,3,2,1,4);

# Complicated Ordering

select *
from `ac2_graduations` 
order by (
    CASE `is_scheduled`
        WHEN 1 THEN
            CASE `is_offline`
                WHEN 0 THEN 1
                ELSE 2
            END
        ELSE
            CASE `taken_exam`
                WHEN 1 THEN
                    CASE `is_offline`
                        WHEN 0 THEN 3
                        ELSE  4
                    END
                ELSE 10
            END
    END 
)

# View

CREATE VIEW PostCountPerCategoryView 
AS
SELECT  c.id, c.title, count(1) as count
FROM post p 
JOIN category c ON c.id = p.cat_id 
GROUP BY c.id, c.title
ORDER BY count DESC

# Lock rows

# FOR UPDATE

SELECT * FROM table_name WHERE id=10 FOR UPDATE;

will not allow other transactions to read, update or delete the row

SELECT * FROM table_name WHERE id=10 LOCK IN SHARE MODE

will allow other transaction to read the locked row
but it will not allow other transaction to update or delete the row

# Joins

joins

# Index

Primary key - no duplication, has not null

ALTER TABLE tbl_name ADD PRIMARY KEY (column 1, column 2,..)

Unique - no duplication, has null

ALTER TABLE tbl_name ADD UNIQUE KEY (column 1, column 2,..)

Index - has duplication, has null

ALTER TABLE tbl_name ADD INDEX index_name (column 1, column 2,..)

Fulltext - Use for searching document

ALTER TABLE tbl_name ADD FULLTEXT index_name (column 1, column 2,..)

Drop index

DROP INDEX index_name;

# Commands

# Import Sql

# Change Data Capture (CDC)

# 1. Triggers

CREATE TRIGGER capture_users_changes
AFTER INSERT, UPDATE, DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_audit (user_id, action, old_value, new_value, timestamp)
  VALUES (NEW.id, 
          CASE 
            WHEN OLD.id IS NULL THEN 'INSERT'
            WHEN NEW.deleted_at IS NOT NULL THEN 'DELETE'
            ELSE 'UPDATE'
          END,
          OLD.name,
          NEW.name,
          NOW());
END;

# 2. Binary Logs

SET GLOBAL binlog_format = 'ROW';
SET GLOBAL log_bin = ON;

This enables row-based binary logging, which logs the actual changes to the rows in the database. You can then use the mysqlbinlog utility to read the binary logs and extract the changes you are interested in.

# 3. Using Third-Party Tools

Debezium
MaxScale
MySQL Enterprise Audit

# Hot Fix

Error: Expression #1 of ORDER BY clause is not in SELECT list, references column 'test_wp3.dsm.Order'

Solution:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Error: ERROR 1418 (HY000) at line 64: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)