MySQL Advanced Features

-- Numeric types
INT                     -- 4-byte integer
BIGINT                  -- 8-byte integer
DECIMAL(10,2)           -- Exact decimal
FLOAT / DOUBLE          -- Floating point
BIT(8)                  -- Bit values

-- String types
VARCHAR(255)            -- Variable length (max 65,535)
CHAR(10)               -- Fixed length
TEXT                   -- 65,535 chars
MEDIUMTEXT             -- 16 MB
LONGTEXT               -- 4 GB

-- Date/Time types  
DATE                   -- YYYY-MM-DD
TIME                   -- HH:MM:SS
DATETIME               -- YYYY-MM-DD HH:MM:SS
TIMESTAMP              -- Auto-update capable
YEAR                   -- Year value

-- DETAILED_TAB:
-- AUTO_INCREMENT
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP
);

-- ENUM and SET
CREATE TABLE products (
  status ENUM('active', 'inactive', 'pending'),
  tags SET('new', 'sale', 'featured')
);

-- Binary types
BINARY(16)             -- Fixed binary (UUID)
VARBINARY(255)         -- Variable binary
BLOB, MEDIUMBLOB, LONGBLOB  -- Binary objects

-- Check storage engine
SHOW TABLE STATUS WHERE Name = 'users';
SHOW ENGINES;

-- Create with specific engine
CREATE TABLE transactions (
  id INT PRIMARY KEY,
  amount DECIMAL(10,2)
) ENGINE=InnoDB;  -- Default, supports transactions

CREATE TABLE logs (
  id INT PRIMARY KEY,
  message TEXT
) ENGINE=MyISAM;  -- Fast, no transactions

-- Change storage engine
ALTER TABLE users ENGINE=InnoDB;

-- DETAILED_TAB:
-- InnoDB features (default)
• Transactions (ACID compliant)
• Foreign keys
• Row-level locking
• Crash recovery
• Better for write-heavy

-- MyISAM features
• Table-level locking only
• No transactions
• Smaller disk footprint
• Better for read-heavy
• Full-text indexing (older MySQL)

-- Memory engine
CREATE TABLE cache (
  key_name VARCHAR(255) PRIMARY KEY,
  value TEXT
) ENGINE=MEMORY;  -- RAM storage, lost on restart

-- Convert all tables to InnoDB
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema = 'your_db';

-- Create table with JSON
CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data JSON NOT NULL
);

-- Insert JSON
INSERT INTO events (data) VALUES
  ('{"user": "john", "action": "login"}'),
  (JSON_OBJECT('user', 'jane', 'action', 'purchase'));

-- Query JSON
SELECT data->>'$.user' AS username
FROM events
WHERE data->>'$.action' = 'login';

-- Update JSON field
UPDATE events
SET data = JSON_SET(data, '$.status', 'processed')
WHERE id = 1;

-- DETAILED_TAB:
-- JSON functions
SELECT 
  JSON_EXTRACT(data, '$.user') AS user,
  JSON_TYPE(data),
  JSON_VALID(data),
  JSON_LENGTH(data),
  JSON_KEYS(data)
FROM events;

-- JSON array operations
SELECT data->>'$.items[0]' AS first_item
FROM events
WHERE JSON_CONTAINS(data, '"book"', '$.items');

-- Create generated column from JSON
ALTER TABLE events
ADD COLUMN username VARCHAR(255) 
  GENERATED ALWAYS AS (data->>'$.user') STORED,
ADD INDEX idx_username(username);

-- JSON aggregation
SELECT JSON_ARRAYAGG(data) AS all_events
FROM events;

-- Primary key (clustered)
ALTER TABLE users ADD PRIMARY KEY (id);

-- Unique index
CREATE UNIQUE INDEX uk_email ON users(email);

-- Composite index
CREATE INDEX idx_name ON users(last_name, first_name);

-- Prefix index (for long strings)
CREATE INDEX idx_email_prefix ON users(email(10));

-- Fulltext index
CREATE FULLTEXT INDEX ft_content 
ON articles(title, content);

-- Spatial index
CREATE SPATIAL INDEX sp_location ON stores(location);

-- DETAILED_TAB:
-- Show indexes
SHOW INDEX FROM users;

-- Force/Ignore index
SELECT * FROM users 
USE INDEX (idx_email)
WHERE email = 'john@example.com';

SELECT * FROM users
IGNORE INDEX (idx_name)
WHERE last_name = 'Smith';

-- Invisible index (MySQL 8.0+)
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;

-- Descending index (MySQL 8.0+)
CREATE INDEX idx_created ON orders(created_at DESC);

-- Functional index (MySQL 8.0+)
CREATE INDEX idx_month 
ON sales((MONTH(sale_date)));

-- Explain query
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 'pending';

-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Query profiling
SET profiling = 1;
SELECT * FROM large_table WHERE status = 'active';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- DETAILED_TAB:
-- Optimizer hints (MySQL 8.0+)
SELECT /*+ INDEX(users idx_email) */ *
FROM users WHERE email = 'john@example.com';

-- Analyze table statistics
ANALYZE TABLE users;
OPTIMIZE TABLE users;  -- Defragment

-- Query cache (removed in 8.0 - variables no longer exist)
SHOW VARIABLES LIKE 'query_cache%';

-- Performance schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Find tables without primary key
SELECT tables.table_schema, tables.table_name
FROM information_schema.tables
LEFT JOIN information_schema.key_column_usage AS c
ON tables.table_name = c.table_name
  AND c.constraint_name = 'PRIMARY'
WHERE tables.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
  AND c.constraint_name IS NULL;

-- Range partitioning
CREATE TABLE orders (
  id INT,
  created DATE,
  amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(created)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- List partitioning
CREATE TABLE users_by_region (
  id INT,
  region VARCHAR(10)
)
PARTITION BY LIST(region) (
  PARTITION p_us VALUES IN ('US', 'CA'),
  PARTITION p_eu VALUES IN ('UK', 'DE', 'FR'),
  PARTITION p_asia VALUES IN ('JP', 'CN', 'IN')
);

-- DETAILED_TAB:
-- Hash partitioning
CREATE TABLE user_sessions (
  id INT,
  user_id INT,
  data TEXT
)
PARTITION BY HASH(user_id)
PARTITIONS 10;

-- Manage partitions
ALTER TABLE orders ADD PARTITION (
  PARTITION p2025 VALUES LESS THAN (2026)
);

ALTER TABLE orders DROP PARTITION p2022;

-- Query specific partition
SELECT * FROM orders PARTITION (p2024)
WHERE created >= '2024-01-01';

-- Check partition info
SELECT table_name, partition_name, table_rows
FROM information_schema.partitions
WHERE table_schema = 'your_db';

-- Start transaction
START TRANSACTION;  -- or BEGIN
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback on error
START TRANSACTION;
UPDATE inventory SET quantity = quantity - 1 WHERE id = 123;
-- Error occurs
ROLLBACK;

-- Savepoints
START TRANSACTION;
UPDATE users SET credits = 100;
SAVEPOINT before_delete;
DELETE FROM logs;
ROLLBACK TO SAVEPOINT before_delete;
COMMIT;

-- DETAILED_TAB:
-- Isolation levels
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- Default
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Check current level
SELECT @@transaction_isolation;

-- Autocommit control
SET autocommit = 0;  -- Manual commit required
SET autocommit = 1;  -- Default, auto-commit each statement

-- Transaction with lock timeout
SET innodb_lock_wait_timeout = 5;
START TRANSACTION;
-- Operations here
COMMIT;

-- Row-level locking (InnoDB)
START TRANSACTION;
SELECT * FROM orders 
WHERE id = 123 
FOR UPDATE;  -- Exclusive lock
-- Do work
COMMIT;

-- Shared lock
SELECT * FROM products
WHERE category = 'electronics'
FOR SHARE;  -- Multiple reads OK

-- Table locks
LOCK TABLES users WRITE, orders READ;
-- Do work
UNLOCK TABLES;

-- DETAILED_TAB:
-- Skip locked rows (MySQL 8.0+)
SELECT * FROM orders
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- Nowait option (MySQL 8.0+)
SELECT * FROM inventory
WHERE product_id = 456
FOR UPDATE NOWAIT;  -- Fail immediately if locked

-- Check locks
SHOW ENGINE INNODB STATUS;

-- Find blocking queries
SELECT * FROM performance_schema.data_lock_waits;

-- Kill blocking connection
SHOW PROCESSLIST;
KILL CONNECTION 1234;

-- Deadlock retry pattern
DELIMITER $$
CREATE PROCEDURE transfer_with_retry()
BEGIN
  DECLARE retry_count INT DEFAULT 3;
  DECLARE CONTINUE HANDLER FOR 1213  -- Deadlock error
    BEGIN
      SET retry_count = retry_count - 1;
      IF retry_count > 0 THEN
        ROLLBACK;
        START TRANSACTION;
      END IF;
    END;
  -- Transaction logic here
END$$

-- Simple procedure
DELIMITER $$
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
  SELECT * FROM users WHERE id = user_id;
END$$
DELIMITER ;

-- Call procedure
CALL GetUserById(123);

-- Procedure with OUT parameter
DELIMITER $$
CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM users;
END$$
DELIMITER ;

-- Call with output
CALL GetUserCount(@count);
SELECT @count;

-- DETAILED_TAB:
-- Complex procedure with error handling
DELIMITER $$
CREATE PROCEDURE TransferFunds(
  IN from_account INT,
  IN to_account INT,
  IN amount DECIMAL(10,2)
)
BEGIN
  DECLARE exit handler for sqlexception
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;
  
  START TRANSACTION;
  
  UPDATE accounts 
  SET balance = balance - amount
  WHERE id = from_account AND balance >= amount;
  
  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;
  
  UPDATE accounts
  SET balance = balance + amount
  WHERE id = to_account;
  
  COMMIT;
END$$
DELIMITER ;

-- Create function
DELIMITER $$
CREATE FUNCTION CalculateTax(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * 0.08;
END$$
DELIMITER ;

-- Use function
SELECT price, CalculateTax(price) as tax
FROM products;

-- Create trigger
DELIMITER $$
CREATE TRIGGER update_modified
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.modified_at = NOW();
END$$
DELIMITER ;

-- DETAILED_TAB:
-- Complex trigger with logging
DELIMITER $$
CREATE TRIGGER audit_user_changes
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (
    table_name,
    record_id,
    action,
    old_values,
    new_values,
    changed_by,
    changed_at
  ) VALUES (
    'users',
    NEW.id,
    'UPDATE',
    JSON_OBJECT('name', OLD.name, 'email', OLD.email),
    JSON_OBJECT('name', NEW.name, 'email', NEW.email),
    USER(),
    NOW()
  );
END$$
DELIMITER ;

-- List triggers
SHOW TRIGGERS;
DROP TRIGGER IF EXISTS trigger_name;

-- On source server
-- Edit my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

-- Create replication user
CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

-- Get binary log status
SHOW BINARY LOG STATUS;
-- Note File and Position

-- On replica server
-- Edit my.cnf
[mysqld]
server-id=2

-- Configure replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source_ip',
  SOURCE_USER='replica',
  SOURCE_PASSWORD='password',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=154;

-- Start replication
START REPLICA;
SHOW REPLICA STATUS\G

-- DETAILED_TAB:
-- Check replication lag
SHOW REPLICA STATUS\G
-- Look for Seconds_Behind_Source

-- Skip replication errors
STOP REPLICA;
SET GLOBAL SQL_REPLICA_SKIP_COUNTER = 1;
START REPLICA;

-- Reset replication
STOP REPLICA;
RESET REPLICA ALL;

-- Semi-synchronous replication
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

-- Read from replica, write to source pattern
-- Application logic:
-- Writes: connect to source
-- Reads: connect to replica (load balance)

-- Logical backup with mysqldump
mysqldump -u root -p database_name > backup.sql
mysqldump -u root -p --all-databases > all_backup.sql

-- Backup with compression
mysqldump -u root -p database_name | gzip > backup.sql.gz

-- Backup specific tables
mysqldump -u root -p database_name table1 table2 > tables.sql

-- Restore from backup
mysql -u root -p database_name < backup.sql

-- DETAILED_TAB:
-- Consistent backup with single transaction
mysqldump --single-transaction --quick \
  --lock-tables=false -u root -p database > backup.sql

-- Binary backup with Percona XtraBackup
xtrabackup --backup --target-dir=/backup/
xtrabackup --prepare --target-dir=/backup/
xtrabackup --copy-back --target-dir=/backup/

-- Point-in-time recovery
-- 1. Restore full backup
mysql -u root -p < full_backup.sql
-- 2. Apply binary logs
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

-- Export/Import with MySQL Shell
mysqlsh -u root -p
util.dumpSchemas(['database'], '/backup/dir')
util.loadDump('/backup/dir')

-- Verify backup
mysql -u root -p -e "SELECT COUNT(*) FROM database.table"

-- Create user
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'app'@'%' IDENTIFIED BY 'secure_password';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE ON database.* TO 'john'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Specific table permissions
GRANT SELECT, INSERT ON database.users TO 'app'@'%';

-- Show grants
SHOW GRANTS FOR 'john'@'localhost';

-- Revoke privileges
REVOKE INSERT ON database.* FROM 'john'@'localhost';

-- DETAILED_TAB:
-- Password management
ALTER USER 'john'@'localhost' IDENTIFIED BY 'new_password';

-- Password expiry
ALTER USER 'john'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Account locking
ALTER USER 'john'@'localhost' ACCOUNT LOCK;
ALTER USER 'john'@'localhost' ACCOUNT UNLOCK;

-- Resource limits
ALTER USER 'app'@'%' WITH
  MAX_QUERIES_PER_HOUR 1000
  MAX_CONNECTIONS_PER_HOUR 100
  MAX_USER_CONNECTIONS 10;

-- List all users
SELECT user, host FROM mysql.user;

-- Drop user
DROP USER 'john'@'localhost';

-- Flush privileges (if modifying mysql tables directly)
FLUSH PRIVILEGES;

-- Show current connections
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- Kill long-running query
KILL QUERY 1234;
KILL CONNECTION 1234;

-- Server status
SHOW STATUS;
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Variables
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb%';

-- DETAILED_TAB:
-- Performance schema queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Table sizes
SELECT 
  table_schema AS 'Database',
  table_name AS 'Table',
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY (data_length + index_length) DESC;

-- Connection stats
SHOW STATUS WHERE Variable_name IN (
  'Connections',
  'Max_used_connections',
  'Aborted_connects',
  'Threads_connected'
);

-- InnoDB metrics
SHOW ENGINE INNODB STATUS\G

-- Buffer pool efficiency
SELECT 
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 
  AS buffer_pool_hit_rate
FROM (
  SELECT 
    MAX(IF(variable_name = 'Innodb_buffer_pool_reads', variable_value, NULL)) AS Innodb_buffer_pool_reads,
    MAX(IF(variable_name = 'Innodb_buffer_pool_read_requests', variable_value, NULL)) AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
  WHERE variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')
) AS t;