MariaDB数据库操作 #

MariaDB架构
├── 服务器（Server）
│   └── MariaDB服务进程
├── 数据库（Database）
│   ├── 系统数据库
│   │   ├── mysql（用户权限）
│   │   ├── information_schema（元数据）
│   │   ├── performance_schema（性能监控）
│   │   └── sys（系统视图）
│   └── 用户数据库
│       ├── mydb1
│       ├── mydb2
│       └── ...
└── 表（Table）
    ├── users
    ├── orders
    └── ...

CREATE DATABASE [IF NOT EXISTS] database_name
    [CHARACTER SET charset_name]
    [COLLATE collation_name];

-- 创建数据库
CREATE DATABASE mydb;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS mydb;

-- 指定字符集
CREATE DATABASE mydb 
CHARACTER SET utf8mb4;

-- 指定字符集和排序规则
CREATE DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 完整示例
CREATE DATABASE IF NOT EXISTS mydb 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_unicode_ci;

-- 常用字符集
-- utf8mb4：完整的UTF-8编码（推荐）
-- utf8：MySQL的utf8是utf8mb3，不完整
-- latin1：西欧字符集
-- gbk：中文GBK编码

-- 查看支持的字符集
SHOW CHARACTER SET;

-- 查看支持的排序规则
SHOW COLLATION;

-- 查看特定字符集的排序规则
SHOW COLLATION WHERE Charset = 'utf8mb4';

-- 常用排序规则
-- utf8mb4_general_ci：通用排序，不区分大小写（较快）
-- utf8mb4_unicode_ci：Unicode排序，更准确（推荐）
-- utf8mb4_bin：二进制排序，区分大小写
-- utf8mb4_0900_ai_ci：MySQL 8.0新排序规则

-- 排序规则对比
CREATE TABLE collation_test (
    name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT INTO collation_test VALUES ('a'), ('A'), ('ä');

-- utf8mb4_general_ci: a = A (ä可能不同)
-- utf8mb4_unicode_ci: a = A = ä
-- utf8mb4_bin: a ≠ A ≠ ä

-- 查看所有数据库
SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

-- 模糊匹配
SHOW DATABASES LIKE 'my%';

-- 条件查询
SELECT schema_name FROM information_schema.schemata;

SHOW CREATE DATABASE mydb;

+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */|
+----------+----------------------------------------------------------------+

-- 从information_schema查询
SELECT 
    schema_name,
    default_character_set_name,
    default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'mydb';

-- 查看数据库大小
SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema;

-- 选择数据库
USE mydb;

-- 查看当前数据库
SELECT DATABASE();

-- 创建并选择
CREATE DATABASE mydb;
USE mydb;

# 连接时指定数据库
mariadb -u root -p mydb

# 执行SQL文件
mariadb -u root -p mydb < script.sql

-- 修改字符集
ALTER DATABASE mydb 
CHARACTER SET utf8mb4;

-- 修改字符集和排序规则
ALTER DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 查看修改后的设置
SHOW CREATE DATABASE mydb;

-- 注意：修改数据库字符集不会影响已存在的表
-- 已存在的表需要单独修改

-- 删除数据库
DROP DATABASE mydb;

-- 如果存在则删除
DROP DATABASE IF EXISTS mydb;

-- 删除前确认
SHOW DATABASES;
SELECT DATABASE();

-- 删除会删除所有数据，谨慎操作
-- 建议先备份
-- mysqldump -u root -p mydb > mydb_backup.sql

-- 删除后无法恢复（除非有备份）
DROP DATABASE IF EXISTS mydb;

-- 合法的数据库命名
CREATE DATABASE mydb;           -- 小写字母
CREATE DATABASE my_db;          -- 下划线分隔
CREATE DATABASE mydb2;          -- 包含数字
CREATE DATABASE `my-db`;        -- 特殊字符（反引号）

-- 不推荐
CREATE DATABASE MyDB;           -- 大小写混用
CREATE DATABASE myDb;           -- 驼峰命名
CREATE DATABASE `my db`;        -- 包含空格

-- 推荐命名示例
CREATE DATABASE shop_system;
CREATE DATABASE blog_platform;
CREATE DATABASE user_center;
CREATE DATABASE order_service;

-- 创建数据库时设置选项
CREATE DATABASE mydb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci
    ENCRYPTION = 'Y';  -- MariaDB 10.2+

-- 查看数据库选项
SHOW CREATE DATABASE mydb;

-- 设置数据库只读（MariaDB 10.4+）
ALTER DATABASE mydb READ ONLY = 1;

-- 取消只读
ALTER DATABASE mydb READ ONLY = 0;

# 备份单个数据库
mariadb-dump -u root -p mydb > mydb_backup.sql

# 备份多个数据库
mariadb-dump -u root -p --databases mydb1 mydb2 > multi_db_backup.sql

# 备份所有数据库
mariadb-dump -u root -p --all-databases > all_db_backup.sql

# 只备份结构
mariadb-dump -u root -p --no-data mydb > mydb_structure.sql

# 只备份数据
mariadb-dump -u root -p --no-create-info mydb > mydb_data.sql

# 恢复数据库
mariadb -u root -p mydb < mydb_backup.sql

# 恢复到新数据库
mariadb -u root -p -e "CREATE DATABASE mydb_new"
mariadb -u root -p mydb_new < mydb_backup.sql

-- 使用SELECT INTO OUTFILE
SELECT * FROM users 
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 使用LOAD DATA INFILE恢复
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

# 从MySQL导出
mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb.sql

# 导入到MariaDB
mariadb -u root -p mydb < mydb.sql

# 或直接管道
mysqldump -u root -p mydb | mariadb -u root -p mydb

# 使用兼容模式导出
mariadb-dump -u root -p --compatible=mysql56 mydb > mydb_compatible.sql

# 升级检查
mariadb-upgrade -u root -p

-- 查看所有数据库大小
SELECT 
    table_schema AS database_name,
    COUNT(*) AS table_count,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

-- 查看特定前缀的数据库
SELECT schema_name 
FROM information_schema.schemata 
WHERE schema_name LIKE 'shop_%';

-- 查看数据库连接数
SELECT 
    db,
    COUNT(*) AS connections
FROM information_schema.processlist
WHERE db IS NOT NULL
GROUP BY db;

-- 查看数据库活动
SELECT 
    db,
    state,
    COUNT(*) AS count
FROM information_schema.processlist
WHERE db IS NOT NULL
GROUP BY db, state;

-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user'@'localhost';

-- 授予只读权限
GRANT SELECT ON mydb.* TO 'readonly'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户对数据库的权限
SHOW GRANTS FOR 'user'@'localhost';

-- 查看特定数据库权限
SELECT 
    grantee,
    table_schema,
    privilege_type
FROM information_schema.schema_privileges
WHERE table_schema = 'mydb';

-- 创建数据库检查清单
-- 1. 确定数据库名称
-- 2. 选择字符集和排序规则
-- 3. 创建数据库
-- 4. 创建用户并授权
-- 5. 创建表结构
-- 6. 导入初始数据
-- 7. 验证功能

-- 示例脚本
CREATE DATABASE IF NOT EXISTS shop_system
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

CREATE USER IF NOT EXISTS 'shop_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON shop_system.* TO 'shop_user'@'localhost';
FLUSH PRIVILEGES;

USE shop_system;
-- 创建表...

项目数据库命名规范
├── 开发环境：{project}_dev
├── 测试环境：{project}_test
├── 预发布环境：{project}_staging
└── 生产环境：{project}_prod

示例：
├── shop_dev
├── shop_test
├── shop_staging
└── shop_prod