MySQL 基礎知識與小技巧隨筆：用戶建立、索引、視圖、系統表、變數、SQL_MODE 實戰片段

MySQL 日常開發運維零散實用片段匯總：用戶建立授權、索引建立刪除、視圖操作、系統雙表結構、用戶變數賦值、SQL_MODE 動態調整，適合碎片化閱讀鞏固基礎。

一、建立用戶並授權 (標準流程)

CREATE USER 'LAOBAO'@'LOCALHOST' IDENTIFIED BY 'LAOBAO123';
GRANT ALL PRIVILEGES ON *.* TO 'LAOBAO'@'LOCALHOST' WITH GRANT OPTION;
FLUSH PRIVILEGES;

二、連接命令與插入自增

mysql -u root -p TENNIS
INSERT INTO TABLE TEST VALUES (NULL);  -- 自增欄位傳 NULL

三、索引操作

-- 普通索引
CREATE INDEX PENALTIES_AMOUNT ON PENALTIES(AMOUNT);

-- 唯一索引
CREATE UNIQUE INDEX PENALTIES_AMOUNT_UQ ON PENALTIES(AMOUNT);

-- 刪除索引
DROP INDEX PENALTIES_AMOUNT ON PENALTIES;

四、視圖操作

-- 建立視圖
CREATE VIEW NUMBER_SETS (MATCHNO, DIFFERENCE) AS
SELECT MATCHNO, ABS(WON-LOST) FROM MATCHES;

-- 授權視圖
GRANT SELECT, UPDATE ON TEAMS TO LAOBAO2;

-- 刪除視圖/表/索引/庫
DROP TABLE MATCHES;
DROP VIEW NUMBER_SETS;
DROP INDEX PENALTIES_AMOUNT;
DROP DATABASE TENNIS;

五、系統雙表結構

• mysql 資料庫：內建權限、用戶、表結構元數據，結構專有、隱藏
• INFORMATION_SCHEMA：標準 SQL 目錄視圖，結構公開、跨資料庫相容，包含 TABLES、COLUMNS、INDEXES 等視圖

兩者部分重疊，INFORMATION_SCHEMA 為標準查詢介面。

六、列別名使用限制

SELECT col AS alias FROM tbl 中的 alias 可用於 SELECT 列表、ORDER BY、GROUP BY、HAVING，**不可用於 WHERE、FROM** 同層級查詢塊。
SELECT won AS w, w*2 FROM tbl ❌ 同塊不可引用別名。

七、用戶變數賦值

SELECT @creditsum := SUM(credit) FROM artype;
-- @creditsum 為會話級用戶變數，後續查詢可直接引用

八、版本與 SQL_MODE 動態調整

SELECT @@VERSION;

-- 啟用 PIPES_AS_CONCAT (|| 當作字串拼接)
SET @@SQL_MODE = 'PIPES_AS_CONCAT';

-- 疊加模式 (保留原有模式)
SET @@SQL_MODE = CONCAT(@@SQL_MODE,
  CASE @@SQL_MODE WHEN '' THEN '' ELSE ',' END,
  'NO_ZERO_IN_DATE');

總結

碎片知識成體系：用戶授權標準化 → 索引/視圖 DDL → 雙系統表分工 → 別名作用域 → 用戶變數跨語句 → SQL_MODE 精準控制。建議建立個人《MySQL 隨筆庫》，分類收錄此類片段，需要時秒級調用。