Developer's Catalog

Database

MySQL

Overview

MySQL is the world's most popular open-source relational database management system. Since 1995, it has been widely adopted as a core component of web development, particularly known as the "M" in the LAMP (Linux, Apache, MySQL, PHP) stack.

Details

MySQL was developed by MySQL AB in Sweden and is currently owned and developed by Oracle Corporation. Designed with emphasis on speed, reliability, and ease of use, it is used extensively from small web applications to large enterprise systems.

Key features of MySQL:

• High-speed performance
• Multiple storage engines (InnoDB, MyISAM, Memory, etc.)
• Replication and clustering capabilities
• ACID properties support (InnoDB engine)
• Cross-platform compatibility
• Rich programming language support
• Partitioning functionality
• Views, stored procedures, and triggers
• Full-text indexing
• Geographic Information System (GIS) support

Advantages and Disadvantages

Advantages

• High speed: Extremely fast read operations
• Lightweight: Operates with minimal resource requirements
• Ease of use: Low learning curve and easy deployment
• Community: Large community and ecosystem
• Compatibility: Great affinity with many web applications
• Cost-effective: Open-source version is free
• Rich tooling: phpMyAdmin, MySQL Workbench, and more

Disadvantages

• Complex queries: Some advanced SQL features are limited
• NoSQL features: Limited NoSQL capabilities
• Commercial licensing: Commercial license required for some use cases
• Configuration complexity: Optimization for large environments can be complex
• Storage engine dependency: Features depend on storage engine

Key Links

• Official Website
• Documentation
• MySQL Community Server
• GitHub

Code Examples

Installation & Setup

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-server mysql-client

# Red Hat/CentOS
sudo yum install mysql-server mysql
sudo systemctl start mysqld
sudo systemctl enable mysqld

# macOS (Homebrew)
brew install mysql
brew services start mysql

# Docker
docker run --name mysql-db -e MYSQL_ROOT_PASSWORD=mypassword -p 3306:3306 -d mysql:8.0

# Initial setup
sudo mysql_secure_installation

Basic Operations (CRUD)

-- Database connection
mysql -u root -p

-- Create database
CREATE DATABASE myapp;
USE myapp;

-- Create table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data (Create)
INSERT INTO users (name, email) VALUES 
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]');

-- Read data (Read)
SELECT * FROM users;
SELECT * FROM users WHERE name LIKE 'John%';

-- Update data (Update)
UPDATE users SET email = '[email protected]' 
WHERE name = 'John Doe';

-- Delete data (Delete)
DELETE FROM users WHERE id = 1;

Data Modeling

-- Foreign key constraints
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- Create indexes
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(order_date);

-- Create view
CREATE VIEW user_orders AS
SELECT u.name, u.email, o.amount, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Indexing & Optimization

-- Composite index
CREATE INDEX idx_user_date ON orders(user_id, order_date);

-- Full-text index
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
);

-- Check execution plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Update table statistics
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

Practical Examples

-- Using JSON type (MySQL 5.7+)
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES 
('Laptop', '{"brand": "Dell", "cpu": "Intel i7", "ram": "16GB"}');

SELECT * FROM products 
WHERE JSON_EXTRACT(attributes, '$.brand') = 'Dell';

-- Stored procedure
DELIMITER //
CREATE PROCEDURE GetUserOrderTotal(IN user_id_param INT)
BEGIN
    SELECT COALESCE(SUM(amount), 0) as total
    FROM orders 
    WHERE user_id = user_id_param;
END //
DELIMITER ;

CALL GetUserOrderTotal(1);

-- Trigger
DELIMITER //
CREATE TRIGGER update_user_timestamp 
BEFORE UPDATE ON users
FOR EACH ROW 
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END //
DELIMITER ;

Best Practices

-- Transaction management
START TRANSACTION;
INSERT INTO users (name, email) VALUES ('New User', '[email protected]');
INSERT INTO orders (user_id, amount) VALUES (LAST_INSERT_ID(), 1000.00);
COMMIT;

-- Partitioning
CREATE TABLE orders_partitioned (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Replication setup (Master)
-- my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

-- Configuration optimization
-- my.cnf
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
max_connections = 200
query_cache_type = 1
query_cache_size = 128M