快速複製表結構與數據的標準做法：
CREATE TABLE t3 LIKE t1; INSERT INTO t3 SELECT * FROM t1;
-- 普通索引 ALTER TABLE table_name ADD INDEX index_name (column_list); -- 唯一索引 ALTER TABLE table_name ADD UNIQUE (column_list); -- 主鍵索引 ALTER TABLE table_name ADD PRIMARY KEY (column_list); -- CREATE INDEX 方式 CREATE INDEX index_name ON table_name (column_list); CREATE UNIQUE INDEX index_name ON table_name (column_list);
DROP INDEX index_name ON table_name; ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE table_name DROP PRIMARY KEY;
-- 創建視圖 CREATE VIEW v_t1 AS SELECT * FROM t1 WHERE id > 4 AND id < 11; -- 查看視圖 SHOW TABLES; -- 刪除視圖 DROP VIEW v_t1;
CONCAT(str1, str2, ...) -- 連接字符串 LCASE/UCASE(str) -- 大小寫轉換 LENGTH(str) -- 字符串長度 LTRIM/RTRIM(str) -- 去除首尾空格 REPLACE(str, search, replace) -- 字符串替換 SUBSTRING(str, pos, len) -- 子串提取
CEILING/FLOOR(num) -- 向上/向下取整 ROUND(num) -- 四捨五入 RAND() -- 0-1 隨機數 SQRT(num) -- 開平方
NOW() -- 當前日期時間 CURDATE()/CURTIME() -- 當前日期/時間 UNIX_TIMESTAMP(date) -- 轉 Unix 時間戳 FROM_UNIXTIME(ts) -- 時間戳轉日期 DATEDIFF(d1, d2) -- 日期差天數
PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE id > ?'; SET @i = 1; EXECUTE stmt1 USING @i; SET @i = 5; EXECUTE stmt1 USING @i; DEALLOCATE PREPARE stmt1;
SET autocommit = 0; DELETE FROM t1 WHERE id = 11; SAVEPOINT p1; DELETE FROM t1 WHERE id = 10; SAVEPOINT p2; ROLLBACK TO p1; -- 回滾到 p1，p2 自動失效 ROLLBACK; -- 完全回滾
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
SET @i = 0;
WHILE @i < 10 DO
SELECT @i;
SET @i = @i + 1;
END WHILE;
END//
DELIMITER ;
CALL p1();
SHOW CREATE PROCEDURE p1\G
DELIMITER //
-- 插入同步
CREATE TRIGGER tg1 BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
INSERT INTO t2(id) VALUES(NEW.id);
END//
-- 刪除同步
CREATE TRIGGER tg2 BEFORE DELETE ON t1
FOR EACH ROW
BEGIN
DELETE FROM t2 WHERE id = OLD.id;
END//
-- 更新同步
CREATE TRIGGER tg3 BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
UPDATE t2 SET id = NEW.id WHERE id = OLD.id;
END//
DELIMITER ;
SHOW TRIGGERS;
-- 方法一：TRUNCATE（重置自增值為 1） TRUNCATE TABLE tablename; -- 方法二：ALTER TABLE 顯式指定 ALTER TABLE tablename AUTO_INCREMENT = 1;
獲取多維度小計與總計：
SELECT cname, pname, COUNT(pname) FROM demo GROUP BY cname, pname WITH ROLLUP;
結果包含各分組計數、各城市小計（pname 為 NULL）及總計（cname、pname 均為 NULL）。注意：WITH ROLLUP 不可與 ORDER BY 同用。
CREATE TABLE temp (
id INT,
name CHAR(20),
FOREIGN KEY (id) REFERENCES outTable(id)
ON DELETE CASCADE ON UPDATE CASCADE
);
關鍵限制：僅 InnoDB 支持外鍵，MyISAM 雖可創建但不生效。
SHOW [SESSION|GLOBAL] STATUS; SHOW STATUS LIKE 'Com_%'; SHOW GLOBAL STATUS LIKE 'Com_%';
關鍵指標：Com_select、Com_insert、Slow_queries、InnoDB_rows_read 等。
EXPLAIN SELECT * FROM table WHERE id = 1000; -- 或 DESC SELECT * FROM table WHERE id = 1000;
| 列名 | 含義 |
|---|---|
| id | 查詢序號 |
| select_type | 查詢類型：SIMPLE、PRIMARY、UNION、SUBQUERY 等 |
| table | 輸出結果的表 |
| type | 連接類型，性能由好到差：system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能使用的索引 |
| key | 實際使用的索引 |
| key_len | 索引字段長度 |
| ref | 與索引比較的列或常量 |
| rows | 預估掃描行數 |
| Extra | 額外信息：Using index、Using where、Using temporary、Using filesort 等 |
LIKE 'prefix%'（% 不在首位）可用索引；LIKE '%suffix' 則不可column IS NULL 可用索引OR 條件兩側索引不完整導致全表掃描LIKE '%...' 以通配符開頭SHOW STATUS LIKE 'Handler_read%';
Handler_read_key 高 → 索引命中好Handler_read_rnd_next 高 → 全表掃描多，需優化CHECK TABLE tbl_name [QUICK|FAST|MEDIUM|EXTENDED|CHANGED]; ANALYZE TABLE tbl_name;
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name;
適用於大量刪除/更新後的 MyISAM、BDB、InnoDB 表，合併空間碎片。
MyISAM：禁用/啟用非唯一索引
ALTER TABLE tbl_name DISABLE KEYS; LOAD DATA INFILE ...; ALTER TABLE tbl_name ENABLE KEYS;
實測速度提升 10 倍以上。
InnoDB：
SET unique_checks = 0 關閉唯一性檢查SET autocommit = 0 關閉自動提交INSERT INTO ... VALUES (...), (...), ...INSERT DELAYED（MyISAM）bulk_insert_buffer_size（MyISAM）LOAD DATA INFILE，比多條 INSERT 快 20 倍-- 禁止隱式排序 SELECT id, SUM(moneys) FROM sales2 GROUP BY id ORDER BY NULL;
去除 Using filesort，顯著降低開銷。
-- 低效子查詢 SELECT * FROM sales2 WHERE company_id NOT IN (SELECT id FROM company2); -- 高效 LEFT JOIN SELECT * FROM sales2 LEFT JOIN company2 ON sales2.company_id = company2.id WHERE sales2.company_id IS NULL;
JOIN 避免臨時表創建，掃描行數大幅減少。
SELECT * FROM table_name PROCEDURE ANALYSE();
獲取列類型優化建議（如建議改為 ENUM）。
LOCK TABLE t1 READ：共享鎖，適合備份LOCK TABLE t1 WRITE：排他鎖，阻塞所有讀寫-- 啟用 binlog log-bin=mysql-bin -- 查看 SHOW BINARY LOGS; SHOW MASTER STATUS; -- 慢查詢日誌 slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2
# 1. 停止服務
service mysqld stop
# 2. 跳過授權表啟動
mysqld_safe --skip-grant-tables --user=mysql &
# 3. 免密登錄修改密碼
mysql -uroot
UPDATE user SET password=PASSWORD('newpass') WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;
# 4. 重啟服務
service mysqld restart
