您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MariaDB DML 操作與用戶權限管理實戰指南

編輯：MySQL 綜合教學

MariaDB DML 與權限管理：從基礎語法到生產級最佳實踐

MariaDB 作為 MySQL 社區分支，繼承了標準 SQL 語法並增強了存儲引擎插件化特性。本文系統梳理 INSERT/SELECT/DELETE/UPDATE 四大 DML 操作技巧、用戶賬號體系、權限授予與回收全流程，並針對 skip_name_resolve、FLUSH PRIVILEGES 觸發機制等易混淆點給出權威解釋。

一、DML 核心操作實戰

1. INSERT 多樣化寫入

-- 單行全列插入（字符丈需引號，數值無引號）
INSERT INTO students VALUES (1, 'xijinping', 51, 'zhonglanhai', 'm');

-- 多行指定列插入
INSERT INTO students (id, name) VALUES (2, 'hujingtao'), (3, 'jiangzeming');

-- 配合子查詢/選擇插入
INSERT INTO archive_users SELECT * FROM users WHERE create_time < '2023-01-01';

2. SELECT 靈活查詢

-- 全列查詢
SELECT * FROM students;

-- 指定列 + 別名
SELECT id AS stuid, name FROM students;

-- 條件過濾
SELECT * FROM students WHERE id = 1;
SELECT * FROM students WHERE name = 'jiangzemin';  -- 大小寫敏感取決於字符集校對規則

-- 範圍與模糊
SELECT name, age FROM students WHERE age BETWEEN 30 AND 50;
SELECT name FROM students WHERE name LIKE '%ji%';      -- % 任意長度，_ 單字符
SELECT name FROM students WHERE name RLIKE 'min$';     -- 正則匹配

-- 空值判斷
SELECT id, name FROM students WHERE age IS NULL;

-- 排序
SELECT id, name FROM students ORDER BY name DESC;

3. DELETE 精準刪除

-- 條件刪除
DELETE FROM students WHERE age IS NULL;

-- 帶排序限制刪除（防誤刪全表）
DELETE FROM a ORDER BY age DESC LIMIT 20;

-- 清空表（TRUNCATE 更快、重置自增、不觸發觸發器）
TRUNCATE TABLE students;

4. UPDATE 安全更新

-- 帶條件、排序、限制
UPDATE a SET age = age - 5 ORDER BY id DESC LIMIT 10;
UPDATE a SET age = age - 5 WHERE name NOT LIKE 'stu__';

5. 高效建表技巧

-- 1. 復制結構+索引
CREATE TABLE a LIKE students;

-- 2. 僅復制結構（無索引、無數據）
CREATE TABLE b SELECT * FROM students LIMIT 0;

二、用戶賬號與權限體系

1. 用戶標識：'username'@'host'

host 支持通配符：%（任意 IP）、_（單字符）、192.168.%.%（網段）
性能優化：my.cnf 添加 skip_name_resolve = ON 禁用 DNS 反解析，消除登入延遲。

2. 用戶生命周期管理

-- 創建用戶
CREATE USER 'testuser'@'192.168.%.%' IDENTIFIED BY 'testpass';

-- 修改密碼
SET PASSWORD FOR 'testuser'@'192.168.%.%' = PASSWORD('newpass');
-- 或
ALTER USER 'testuser'@'192.168.%.%' IDENTIFIED BY 'newpass';

-- 刪除用戶
DROP USER 'testuser'@'192.168.%.%';

-- 查看用戶表
SELECT User, Host, plugin, authentication_string FROM mysql.user;

3. 權限授予 (GRANT)

-- 庫級讀寫
GRANT SELECT, INSERT ON test1.students TO 'testuser'@'192.168.%.%';

-- 表級唯讀
GRANT SELECT ON test1.orders TO 'readonly'@'10.0.0.%';

-- 全局管理權限（慎用）
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.0.0.1' WITH GRANT OPTION;

4. 權限查看

SHOW GRANTS FOR 'testuser'@'192.168.%.%';
SHOW GRANTS FOR CURRENT_USER();

5. 權限回收 (REVOKE)

REVOKE INSERT ON test1.students FROM 'testuser'@'192.168.%.%';

三、權限生效機制深度解析

操作方式	是否需手動 FLUSH PRIVILEGES	說明
GRANT / REVOKE / CREATE USER / DROP USER / ALTER USER	否（自動刷新）	MariaDB/MySQL 服務進程內存權限緩存自動同步
直接 DML 操作 mysql.user/db/tables_priv/columns_priv/procs_priv 表	是	繞過 SQL 層，需手動 `FLUSH PRIVILEGES` 或 `mysqladmin flush-privileges`

四、安裝後安全初始化（必做項）

# 1. 運行安全腳本（交互式設置 root 密碼、移除匿名用戶、禁用 root 遠程、刪除 test 庫）
mysql_secure_installation

# 2. 或手動設置 root 密碼
mysql -u root -p  # 初始密碼為空或查看 ~/.mysql_secret
SET PASSWORD = PASSWORD('StrongP@ssw0rd');
FLUSH PRIVILEGES;

五、MariaDB vs MySQL 關鍵差異速查

特性	MySQL	MariaDB
默認存儲引擎	InnoDB	InnoDB (兼容 XtraDB)
MyISAM 替代	MyISAM	Aria (改進版，支持崩潰恢復)
InnoDB 替代	InnoDB	XtraDB (Percona 改進版，性能更優)
開源協議	GPL + 商業雙授權	純 GPL，完全開源
新特性	CTE、窗口函數、JSON、GIS	同上 + ColumnStore、Spider、更多測試組件
安裝包命名	mysql-community-server	mariadb-server

六、總結

MariaDB DML 語法與 MySQL 高度兼容，核心差異在於存儲引擎生態與開源策略。權限管理遵循 'user'@'host' 雙元組模型，**牢記：SQL 層授權命令自動生效，直接操作權限表必須手動 FLUSH**。生產環境請落地：skip_name_resolve=ON、validate_password 強密碼、mysql_secure_installation 基線加固、定期 SHOW GRANTS 審計。