MariaDB

技术栈

数据库

mariadbrdbmssqlmysql-compatibleopen-source

概览

MariaDB 是 MySQL 的开源分支，由 MySQL 创始人 Michael "Monty" Widenius 在 MySQL 被 Oracle 收购后创建，旨在保持开源自由和高兼容性。它与 MySQL API/协议完全兼容（可直接替换），同时引入了更多存储引擎（Aria、ColumnStore）、更好的查询优化器和 JSON 支持，是 Debian、Fedora、openSUSE 等发行版的默认 MySQL 替代，被 Wikipedia、Google、Mozilla 等广泛采用。

安装

1. 环境准备

操作系统：Linux (Ubuntu 20.04+/CentOS 7+)、macOS 12+、Windows 10+
硬件要求：最低 512MB RAM，1GB 磁盘空间
端口：默认 3306（与 MySQL 相同）
依赖：libaio（Linux）

2. 安装命令

Ubuntu/Debian

sudo apt update
sudo apt install mariadb-server -y
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo mysql_secure_installation  # 设置 root 密码

macOS (Homebrew)

brew install mariadb
brew services start mariadb
mysql_secure_installation

Windows

choco install mariadb
# 或下载 MSI：https://mariadb.org/download/

Docker（推荐开发环境）

docker run --name mariadb-dev \
  -e MARIADB_ROOT_PASSWORD=root123 \
  -p 3306:3306 -v ~/mariadb-data:/var/lib/mysql \
  -d mariadb:11

验证安装

mysql -u root -p
# 或 Docker：
docker exec -it mariadb-dev mysql -u root -proot123

3. 常见安装问题

Q: MariaDB 和 MySQL 同时安装冲突
端口冲突（都占 3306）。修改 MariaDB 端口：
编辑 /etc/mysql/mariadb.conf.d/50-server.cnf，将 port = 3306 改为 3307，重启服务。

Q: 从 MySQL 迁移到 MariaDB

# 导出 MySQL 数据
mysqldump -u root -p --all-databases >; backup.sql

# 导入到 MariaDB
mysql -u root -p <; backup.sql
# MariaDB 完全兼容 MySQL 导出文件

Q: Galera Cluster 多节点同步
MariaDB 内置 Galera 集群支持（企业特性）：

# /etc/mysql/conf.d/galera.cnf
[mysqld]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://node1,node2,node3"

示例

目标

通过 MariaDB CLI 和 Python 驱动完成数据库创建、CRUD 操作，体验 MariaDB 与 MySQL 的命令兼容性。

💡 MariaDB 的客户端命令、SQL 语法与 MySQL 完全相同，所有 MySQL 工具（mysql、mysqldump 等）均适用于 MariaDB。

环境准备

mysql -u root -p
# 或 Docker：
docker exec -it mariadb-dev mysql -u root -proot123

第一步：CLI 操作

-- 创建数据库（MariaDB 独有：支持 OR REPLACE）
CREATE OR REPLACE DATABASE hello_mariadb CHARACTER SET utf8mb4;
USE hello_mariadb;

-- 创建表（体验 MariaDB CHECK 约束）
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2) CHECK(salary > 0),
    hire_date DATE DEFAULT (CURRENT_DATE),
    INDEX idx_dept (department)
);

-- 插入
INSERT INTO employees (name, department, salary) VALUES
    ('张三', '技术部', 15000.00),
    ('李四', '市场部', 12000.00),
    ('王五', '技术部', 18000.00),
    ('赵六', '人事部', 10000.00);

-- 查询
SELECT * FROM employees ORDER BY salary DESC;

-- MariaDB 独有：EXCEPT（差集）
SELECT department FROM employees WHERE department = '技术部';

-- 聚合
SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING cnt >= 1
ORDER BY avg_sal DESC;

-- 窗口函数（MariaDB 10.2+ 支持）
SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

第二步：Python 操作

pip install mariadb  # 推荐使用 MariaDB 官方 Connector

import mariadb
import sys

try:
    conn = mariadb.connect(
        host="localhost",
        user="root",
        password="root123",
        database="hello_mariadb"
    )
except mariadb.Error as e:
    print(f"连接错误: {e}")
    sys.exit(1)

cur = conn.cursor()

# 创建表
cur.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        price DECIMAL(8,2) CHECK(price >= 0),
        category VARCHAR(30),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")

# 批量插入
products = [
    ("机械键盘", 299.00, "外设"),
    ("无线鼠标", 89.00, "外设"),
    ("显示器", 1999.00, "显示"),
]
cur.executemany(
    "INSERT INTO products (name, price, category) VALUES (?, ?, ?)",
    products
)
conn.commit()

# 查询 + 参数化
cur.execute(
    "SELECT name, price FROM products WHERE price > ? ORDER BY price DESC",
    (100,)
)
for name, price in cur.fetchall():
    print(f"{name}: ¥{price}")

# 事务
try:
    cur.execute("UPDATE products SET price = price * 0.9 WHERE category = ?", ("外设",))
    cur.execute("SELECT ROW_COUNT()")
    updated = cur.fetchone()[0]
    print(f"打折影响 {updated} 行")
    conn.commit()
except:
    conn.rollback()

cur.close()
conn.close()

第三步：使用 RETURNING 子句（MariaDB 独有）

-- MariaDB 10.5+ 支持 RETURNING
INSERT INTO products (name, price, category)
VALUES ('USB Hub', 49.00, '外设')
RETURNING id, name, created_at;
-- 直接返回新插入行的数据，无需再 SELECT

预期输出

# CLI 窗口函数
name   | department | salary  | dept_rank
-------|------------|---------|-----------
王五   | 技术部     | 18000.00| 1
张三   | 技术部     | 15000.00| 2
李四   | 市场部     | 12000.00| 1
赵六   | 人事部     | 10000.00| 1

# Python
显示器: ¥1999.00
机械键盘: ¥299.00
打折影响 2 行

教程

1. 什么是 MariaDB？

MariaDB 是 MySQL 的完全兼容替代品，由 MySQL 之父 Monty Widenius 在 Oracle 收购 Sun/MySQL 后创建（2009 年），以保障 MySQL 生态的开源自由。

MySQL vs MariaDB

维度	MySQL 8.0	MariaDB 11.x
JSON 支持	JSON 类型 + 函数	相同，还增加了 JSON 校验约束
存储引擎	以 InnoDB 为主	多了 Aria、ColumnStore、MyRocks
窗口函数	支持	支持（10.2+）
CTE/递归	支持	支持
RETURNING 子句	❌	✅ (10.5+)（省去额外查询）
序列（Sequence）	❌	✅ (10.3+)
系统版本表	❌	✅ (10.3+)（自动保留历史）
许可	GPL	GPL + LGPL（客户端库更宽松）

2. MariaDB 独有特性详解

RETURNING 子句

-- 插入同时返回数据，无需 SELECT
INSERT INTO users (name, email) VALUES ('张三', 'zs@test.com')
RETURNING id, name, created_at;

序列（Sequence）

CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;
SELECT NEXTVAL(order_seq);  -- 1000

CREATE TABLE orders (
    order_no BIGINT DEFAULT NEXTVAL(order_seq),
    user_id INT,
    amount DECIMAL(10,2)
);

系统版本表（自动记录历史）

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(8,2)
) WITH SYSTEM VERSIONING;

-- 更新后查询历史
SELECT * FROM products FOR SYSTEM_TIME ALL
WHERE id = 1 AND price < 200;

3. 实战：图书管理系统

CREATE DATABASE library CHARACTER SET utf8mb4;
USE library;

-- 读者表
CREATE TABLE readers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    card_no VARCHAR(20) UNIQUE NOT NULL,
    phone VARCHAR(20),
    registered_at DATE DEFAULT (CURRENT_DATE)
);

-- 图书表（含系统版本追踪）
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    isbn VARCHAR(20) UNIQUE NOT NULL,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    price DECIMAL(8,2),
    stock INT DEFAULT 0 CHECK(stock >= 0)
) WITH SYSTEM VERSIONING;

-- 借阅记录
CREATE TABLE borrows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reader_id INT NOT NULL,
    book_id INT NOT NULL,
    borrow_date DATE DEFAULT (CURRENT_DATE),
    return_date DATE,
    FOREIGN KEY (reader_id) REFERENCES readers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

-- 借书（事务）
DELIMITER $$
CREATE PROCEDURE borrow_book(
    IN p_reader_id INT,
    IN p_book_id INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

    START TRANSACTION;

    -- 检查库存
    IF (SELECT stock FROM books WHERE id = p_book_id) <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
    END IF;

    -- 扣减库存
    UPDATE books SET stock = stock - 1 WHERE id = p_book_id;

    -- 创建借阅记录
    INSERT INTO borrows (reader_id, book_id) VALUES (p_reader_id, p_book_id);

    COMMIT;
END$$
DELIMITER ;

-- 调用
INSERT INTO readers (name, card_no) VALUES ('张三', 'R2024001');
INSERT INTO books (isbn, title, author, price, stock) VALUES
    ('978-7-111', '数据库系统概念', 'Silberschatz', 99.00, 3);
CALL borrow_book(1, 1);

4. ColumnStore 列式引擎（分析场景）

-- MariaDB 10.5+ 内置 ColumnStore
CREATE TABLE sales_log (
    id INT AUTO_INCREMENT,
    product_id INT,
    amount DECIMAL(10,2),
    sale_date DATE,
    region VARCHAR(50)
) ENGINE=ColumnStore;

-- 列式存储对聚合查询极快
SELECT region, SUM(amount) AS total
FROM sales_log
WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY region;

5. 优化技巧

-- 慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 查看执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM borrows WHERE reader_id = 1;

-- 使用 ANALYZE + FORMAT=JSON（MariaDB 独有）
ANALYZE FORMAT=JSON
SELECT b.title, r.name
FROM borrows br
JOIN books b ON br.book_id = b.id
JOIN readers r ON br.reader_id = r.id;

思考题

系统版本表（SYSTEM VERSIONING）和外键约束能共存吗？为什么？
什么场景下 ColumnStore 比 InnoDB 更快？反过来呢？
MariaDB 的 RETURNING 子句相比 MySQL 的 LAST_INSERT_ID() 的 SELECT，有什么优势？

参考资料

[1] MariaDB Foundation. MariaDB Documentation. 2023. https://mariadb.com/kb/en/
[2] Daniel Bartholomew. MariaDB Cookbook. 2014.
[3] MariaDB Foundation. MariaDB vs MySQL - Feature Comparison. 2023. https://mariadb.com/kb/en/mariadb-vs-mysql-features/