MySQL 8.0 to 8.4: New Features and Migration Guide

-- ROW_NUMBER()
SELECT 
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- RANK() and DENSE_RANK()
SELECT 
    product_name,
    sales,
    RANK() OVER (ORDER BY sales DESC) as rank,
    DENSE_RANK() OVER (ORDER BY sales DESC) as dense_rank
FROM products;

-- Running totals
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

-- LEAD() and LAG()
SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) as prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) as growth
FROM monthly_sales;

-- FIRST_VALUE() and LAST_VALUE()
SELECT 
    department,
    employee_name,
    salary,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY salary DESC) as highest_paid
FROM employees;

-- Simple CTE
WITH active_users AS (
    SELECT * FROM users WHERE is_active = TRUE
)
SELECT * FROM active_users WHERE created_at > '2025-01-01';

-- Recursive CTE (hierarchy)
WITH RECURSIVE org_chart AS (
    -- Base case
    SELECT employee_id, manager_id, name, 1 as level
    FROM employees WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case
    SELECT e.employee_id, e.manager_id, e.name, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;

-- Multiple CTEs
WITH 
    total_orders AS (
        SELECT user_id, COUNT(*) as order_count
        FROM orders GROUP BY user_id
    ),
    total_spent AS (
        SELECT user_id, SUM(total_amount) as total
        FROM orders GROUP BY user_id
    )
SELECT u.username, t.order_count, ts.total
FROM users u
JOIN total_orders t ON u.id = t.user_id
JOIN total_spent ts ON u.id = ts.user_id;

-- JSON_TABLE (create virtual table from JSON)
SELECT jt.*
FROM json_data,
JSON_TABLE(
    data,
    '$[*]'
    COLUMNS (
        id INT PATH '$.id',
        name TEXT PATH '$.name',
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt;

-- JSON functions
SELECT JSON_OBJECT('name', name, 'email', email) FROM users;

-- Merge JSON
SELECT JSON_MERGE_PATCH(
    '{"a": 1, "b": 2}',
    '{"b": 3, "c": 4}'
);  -- Result: {"a":1,"b":3,"c":4}

-- JSON value extraction with path
SELECT JSON_EXTRACT(content, '$.items[0].price') FROM documents;

-- Update JSON
UPDATE documents
SET content = JSON_SET(content, '$.items[0].price', 29.99)
WHERE id = 1;

-- Create roles
CREATE ROLE 'app_readonly';
CREATE ROLE 'app_readwrite';
CREATE ROLE 'app_admin';

-- Grant privileges to roles
GRANT SELECT ON mydb.* TO 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_readwrite';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_admin';

-- Assign roles to users
GRANT 'app_readwrite' TO 'developer'@'localhost';
GRANT 'app_admin' TO 'dba'@'localhost';

-- Set default role
SET DEFAULT ROLE 'app_readwrite' FOR 'developer'@'localhost';

-- Activate role for current session
SET ROLE 'app_readwrite';

-- View current roles
SELECT CURRENT_ROLE();

-- Show role privileges
SHOW GRANTS FOR 'app_readwrite';

-- Create invisible index
CREATE INDEX idx_users_phone ON users(phone) INVISIBLE;

-- Toggle visibility
ALTER INDEX idx_users_phone VISIBLE;
ALTER INDEX idx_users_phone INVISIBLE;

-- Optimizer won't use invisible index
EXPLAIN SELECT * FROM users WHERE phone = '123-456-7890';
-- Won't use idx_users_phone if invisible

-- Add column instantly (metadata only, no data rewrite)
ALTER TABLE orders 
ADD COLUMN notes TEXT AFTER status;

-- New columns added to end by default
ALTER TABLE orders 
ADD COLUMN reference VARCHAR(50),
ADD COLUMN priority INT DEFAULT 1;

-- This is instant for InnoDB in MySQL 8.0+
-- No table rebuild required

-- Auto increment now persists across restarts
-- Previously could regress after crash

-- View auto increment value
SHOW CREATE TABLE users;

-- Force specific value
ALTER TABLE users AUTO_INCREMENT = 1000;

-- Improved query optimizer
-- Better cost model
-- Enhanced hash join

-- Parallel query execution
-- For large table scans
SELECT /*+ PARALLEL(orders, 4) */ * FROM orders;

-- JSON table performance improvements
-- Faster JSON processing

-- Stronger password validation
-- Improved caching_sha2_password
-- Default authentication plugin

-- Password expiration policies
ALTER USER 'appuser'@'localhost' 
    PASSWORD EXPIRE INTERVAL 90 DAY;

-- Lock user after failed attempts
ALTER USER 'appuser'@'localhost'
    FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3;

-- Better performance schema
-- More granular instrumentation
-- Reduced overhead

-- Enhanced explain output
EXPLAIN ANALYZE 
SELECT * FROM orders WHERE user_id = 1;

-- Additional status variables
SHOW GLOBAL STATUS LIKE 'Innodb%';

-- Create resource groups
CREATE RESOURCE GROUP batch_process
    TYPE = USER
    VCPU = 0-3
    THREAD_PRIORITY = 10;

-- Assign query to resource group
SELECT /*+ RESOURCE_GROUP(batch_process) */ * 
FROM large_table;

-- View resource groups
SELECT * FROM information_schema.RESOURCE_GROUPS;

-- Spatial reference systems
SELECT * FROM information_schema.ST_SPATIAL_REFERENCE_SYSTEMS;

-- Create table with spatial column
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    coordinates POINT NOT NULL SRID 4326,
    area POLYGON
);

-- Create spatial index
CREATE SPATIAL INDEX idx_locations_coords ON locations(coordinates);

-- Insert spatial data
INSERT INTO locations (name, coordinates)
VALUES 
    ('Office', ST_GeomFromText('POINT(37.7749 -122.4194)', 4326)),
    ('Warehouse', ST_GeomFromText('POINT(37.8049 -122.2694)', 4326));

-- Spatial calculations
SELECT ST_Distance(
    (SELECT coordinates FROM locations WHERE name = 'Office'),
    (SELECT coordinates FROM locations WHERE name = 'Warehouse')
) AS distance_meters;

-- Find locations within radius
SELECT * FROM locations
WHERE ST_Distance_Sphere(coordinates, ST_GeomFromText('POINT(37.7749 -122.4194)', 4326)) < 5000;

# 1. Check compatibility
mysql -u root -p -e "SELECT VERSION();"

# 2. Identify deprecated features
SHOW WARNINGS;

# 3. Check for 5.7 only features
# - Query cache (removed in 8.0)
# - ENUM/SET as key
# - Storage engine specific features

# 1. Export from MySQL 5.7
mysqldump -u root -p --all-databases > all_databases.sql

# 2. Export stored procedures/functions
mysqldump -u root -p --routines --triggers --events mydb > mydb_procs.sql

# 3. Install MySQL 8.4
# 4. Import data
mysql -u root -p < all_databases.sql

# 5. Import procedures
mysql -u root -p mydb < mydb_procs.sql

# 6. Run mysql_upgrade
mysql_upgrade -u root -p

-- Issue: Old authentication plugin
ALTER USER 'root'@'localhost' 
    IDENTIFIED WITH caching_sha2_password BY 'password';

-- Issue: Reserved words
-- 'rank' is now a keyword
-- Use backticks: `rank`

-- Issue: GROUP BY behavior
-- MySQL 5.7 ONLY_FULL_GROUP_BY mode
-- Fix: Either use ANY_VALUE() or disable
SET GLOBAL sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');

-- Issue: TIMESTAMP behavior
-- Now uses CURRENT_TIMESTAMP as default
-- Explicitly handle

-- Flexible schema with JSON
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200),
    base_price DECIMAL(10, 2),
    attributes JSON
);

-- Insert with flexible attributes
INSERT INTO products (name, base_price, attributes) VALUES
('T-Shirt', 19.99, '{"colors": ["red", "blue"], "sizes": ["S", "M", "L"]}'),
('Laptop', 999.99, '{"specs": {"ram": "16GB", "storage": "512GB"}}');

-- Query JSON
SELECT * FROM products 
WHERE JSON_CONTAINS(attributes, '"red"', '$.colors');

SELECT 
    name,
    JSON_EXTRACT(attributes, '$.colors') as colors
FROM products;

-- Use EXPLAIN ANALYZE
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE country = 'USA');

-- Use index hints appropriately
SELECT /*+ INDEX(idx_orders_user_id) */ * 
FROM orders WHERE user_id = 1;

-- Avoid SELECT *
SELECT user_id, SUM(total_amount) 
FROM orders GROUP BY user_id;

-- Essential MySQL 8.0 settings
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB
SET GLOBAL innodb_log_file_size = 1073741824;      -- 1GB
SET GLOBAL innodb_flush_log_at_trx_commit = 1;     -- Safe
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;