MySQL 實戰筆記精選：系統時間自動獲取、主鍵索引增刪、存儲過程核心語法與常用函數大全

MySQL 實戰筆記精選：系統時間自動獲取、主鍵索引增刪、存儲過程核心語法與常用函數大全

1. 系統時間自動獲取（TIMESTAMP 特性）

表中**第一個** TIMESTAMP 列在 INSERT/UPDATE 未顯式賦值時自動置為當前時間。

觸發自動更新的場景：

1. INSERT/UPDATE/LOAD DATA 未指定該列值。
2. 顯式設定該列為 NULL。 3. 非第一個 TIMESTAMP 列設為 NULL 或 NOW()。

範例： CREATE TABLE t(ts TIMESTAMP(14) NOT NULL); 插入時自動填充 20240115143025 格式。

2. 主鍵與自增索引增刪

兩類主鍵

• PRIMARY KEY：需手動賦值，不填默認 0 → 重複報錯。
• NOT NULL AUTO_INCREMENT：自動從 1 遞增，無需賦值，推薦。

轉換操作

-- 1. 刪除自增主鍵（先去自增再刪主鍵）
ALTER TABLE tb CHANGE id id INT(10);           -- 去自增
ALTER TABLE tb DROP PRIMARY KEY;               -- 刪主鍵

-- 2. 添加自增主鍵
ALTER TABLE tb ADD PRIMARY KEY (id);
ALTER TABLE tb CHANGE id id INT(10) NOT NULL AUTO_INCREMENT;

普通索引增刪

-- 添加
ALTER TABLE tbl ADD INDEX idx_name (col1, col2);
ALTER TABLE tbl ADD UNIQUE uk_name (col);
ALTER TABLE tbl ADD PRIMARY KEY (col);

-- 刪除
DROP INDEX idx_name ON tbl;
ALTER TABLE tbl DROP INDEX idx_name;
ALTER TABLE tbl DROP PRIMARY KEY;

-- 查看
SHOW INDEX FROM tbl;

3. 存儲過程核心語法速查

創建/調用/刪除

CREATE PROCEDURE sp_name() BEGIN ... END;
CALL sp_name();  -- 必須帶括號
DROP PROCEDURE sp_name;

變量聲明與賦值

DECLARE var INT DEFAULT 0;
SET var = 1;
SELECT COUNT(*) INTO var FROM tbl;

流程控制

-- IF
IF condition THEN ... ELSEIF condition THEN ... ELSE ... END IF;

-- WHILE
[label:] WHILE expr DO ... END WHILE [label];

-- LOOP
[label:] LOOP ... END LOOP [label];

-- REPEAT
[label:] REPEAT ... UNTIL expr END REPEAT [label];

-- 跳出標籤塊
LEAVE label;
ITERATE label;  -- 類似 continue

4. 常用運算符與函數大全

運算符

字符串函數

CONCAT(str1,str2,...)           -- 連接
LENGTH(str)                     -- 字節長度
CHAR_LENGTH(str)                -- 字符長度
SUBSTRING(str, pos, len)        -- 截取（pos從1開始）
TRIM([BOTH|LEADING|TRAILING] [pad] FROM str)
REPLACE(str, from, to)
UCASE/LCASE(str)
LEFT/RIGHT(str, len)
LOCATE(substr, str, [pos])      -- 同 INSTR 但可指定起始位置

數學函數

ABS, CEILING, FLOOR, ROUND(num, dec), POWER, SQRT, RAND(), SIGN, MOD, LEAST, GREATEST
HEX(num/str)  -- 數轉16進制 / 字符串轉ASCII碼
CONV(num, from_base, to_base)   -- 進制轉換

日期時間函數

NOW(), CURDATE(), CURTIME(), CURRENT_TIMESTAMP()
DATE_ADD(date, INTERVAL expr unit)  -- unit: SECOND/MINUTE/HOUR/DAY/WEEK/MONTH/QUARTER/YEAR
DATE_SUB, DATEDIFF, TIMEDIFF
DATE_FORMAT(date, fmt) / STR_TO_DATE(str, fmt)
EXTRACT(unit FROM date)
DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, MICROSECOND
DAYNAME, MONTHNAME, DAYOFWEEK, DAYOFMONTH, DAYOFYEAR, WEEK, QUARTER
LAST_DAY(date)                    -- 本月最後一天
MAKEDATE(year, dayofyear)
MAKETIME(hour, min, sec)
SEC_TO_TIME(sec) / TIME_TO_SEC(time)
CONVERT_TZ(dt, from_tz, to_tz)

5. INTERVAL 單位完整列表

SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR,
MINUTE_SECOND, HOUR_MINUTE, HOUR_SECOND, DAY_HOUR, DAY_MINUTE, DAY_SECOND,
YEAR_MONTH

收藏此筆記，開發調試時 Ctrl+F 秒查，效率翻倍。