數據開發經典技巧：筆畫排序、加密函數、字段獲取、磁盤分區、表比較、殺進程

數據開發經典技巧合集

收錄 6 個高頻實用的 T-SQL (SQL Server) 實用技巧，涵蓋中文排序、密碼加密比對、動態獲取字段、磁盤監控、表數據比較、批量殺進程，可直接應用於運維與開發腳本。

1. 按中文姓氏筆畫排序

-- 從少到多 (簡體/繁體通用)
SELECT * FROM CustomerTable
ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_CI_AS;

-- 從多到少
SELECT * FROM CustomerTable
ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_CI_AS DESC;

說明： Chinese_PRC_Stroke_CI_AS = 簡體中文_筆畫_不區分大小寫_區分重音。適用於通訊錄、花名冊等業務。

2. 密碼加密與驗證 (SQL Server 內建)

-- 加密
SELECT PWDENCRYPT('plain_password') AS encrypted_pwd;
-- 或舊版
SELECT ENCRYPT('plain_password');

-- 驗證: 返回 1 表示匹配
SELECT PWDCOMPARE('plain_password', 'encrypted_pwd_from_db') AS is_match;
-- 1 = 相同, 0 = 不同

注意： 生產系統建議應用層使用 BCrypt/Argon2/SCrypt 等慢哈希，資料庫層加密僅作為輔助或歷史兼容。

3. 動態獲取表所有字段名並構建 SELECT

DECLARE @col_list VARCHAR(MAX) = '';
DECLARE @sql NVARCHAR(MAX);

SELECT @col_list = @col_list + ',' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID('TableA');

SET @sql = 'SELECT ' + STUFF(@col_list, 1, 1, '') + ' FROM TableA';
EXEC sp_executesql @sql;

優點： 避免 SELECT *，動態適應結構變更，生成代碼/導出工具常用。

4. 查看磁盤分區剩餘空間

EXEC master..xp_fixeddrives;
-- 輸出: drive | MB free
-- 範例: C | 12345

可納入監控腳本，低於閾值告警。

5. 快速比較兩表數據是否完全相同

IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA)
   = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB)
    PRINT '數據完全相同';
ELSE
    PRINT '數據存在差異';

原理： BINARY_CHECKSUM(*) 計算行級雜湊，CHECKSUM_AGG 聚合為表級指紋。極快但極微概率碰撞，核心業務建議再用 EXCEPT/INTERSECT 驗證。

6. 批量殺掉 SQL Profiler / 事件探查器進程

DECLARE cur CURSOR GLOBAL FOR
SELECT 'KILL ' + RTRIM(spid)
FROM master.dbo.sysprocesses
WHERE program_name IN ('SQL Profiler', 'SQL 事件探查器');

OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@cmd);
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

現代替代： 使用 sys.dm_exec_sessions + KILL 或 Extended Events 取代 Profiler，減少性能影響。

適用範圍提示

上述技巧基於 **SQL Server (T-SQL)**。MySQL/PostgreSQL/Oracle 相應功能語法不同：

• 筆畫排序：MySQL 無內建，需應用層或自定義函數。
• 加密：MySQL 用 AES_ENCRYPT()/SHA2()，PG 用 pgcrypto 擴展。
• 動態字段：MySQL 用 INFORMATION_SCHEMA.COLUMNS + PREPARE。
• 磁盤空間：MySQL 用 SHOW VARIABLES LIKE 'datadir' + OS 命令，PG 用 pg_tablespace。
• 表比較：通用做法 (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM B EXCEPT SELECT * FROM A)。