從 MySQL 遷移到 PostgreSQL 關鍵查詢差異和注意事項

從 MySQL 切換到 PostgreSQL 需要仔細考慮查詢語法、資料類型和資料庫功能。本指南重點介紹了促進順利遷移過程的主要差異。

---

1. 資料型態對應：

MySQL 和 PostgreSQL 使用不同的資料類型。 對比一下：

MySQL Data Type	PostgreSQL Equivalent	Notes
TINYINT	SMALLINT	Use BOOLEAN for true/false.
DATETIME	TIMESTAMP	Consider TIMESTAMPTZ for timezone awareness.
TEXT	TEXT	Functionally identical.
ENUM	TEXT CHECK constraint	PostgreSQL lacks ENUM; simulate using CHECK constraints.
AUTO_INCREMENT	SERIAL or GENERATED AS IDENTITY	Use SERIAL or GENERATED AS IDENTITY for auto-incrementing keys.
DOUBLE	DOUBLE PRECISION	Direct equivalent.
BLOB	BYTEA	For binary large objects.

---

2. 字串比較中的大小寫敏感性：

區分大小寫的差異：

• MySQL： LIKE 預設不區分大小寫（對於非二進位列）。
• PostgreSQL: LIKE 區分大小寫；使用 ILIKE 進行不區分大小寫的匹配。

通配符： 兩個資料庫都使用 %（零個或多個字元）和 _（單一字元）通配符。

範例：

• MySQL（不區分大小寫）： SELECT * FROM users WHERE name LIKE 'john%';
• PostgreSQL（不區分大小寫的等效項）： SELECT * FROM users WHERE name ILIKE 'john%'; 或 SELECT * FROM users WHERE LOWER(name) LIKE 'john%';

最佳化：為了在 PostgreSQL 中進行高效的不區分大小寫的搜索，建立一個功能索引：CREATE INDEX idx_users_name_lower ON users (LOWER(name));

---

3. 自動遞增主鍵：

• MySQL： 使用 AUTO_INCREMENT。
• PostgreSQL： 使用 SERIAL 或 GENERATED AS IDENTITY。

範例：

• MySQL： CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
• PostgreSQL： CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));

---

4. 字串函數：

PostgreSQL 的字串函數語法可能與 MySQL 略有不同。

MySQL Function	PostgreSQL Equivalent
CONCAT()	CONCAT()
LENGTH()	LENGTH()
SUBSTRING()	SUBSTRING()
LOCATE()	POSITION() or STRPOS()
REPLACE()	REPLACE()

範例： 兩個資料庫都使用相同的 CONCAT()：SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

---

5. 分頁（LIMIT 和 OFFSET）：

兩個資料庫都支援 LIMIT 和 OFFSET，語法相同：SELECT * FROM users LIMIT 10 OFFSET 20;

---

6. 預設值：

PostgreSQL 的預設值處理更加嚴格。

• MySQL： CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
• PostgreSQL： CREATE TABLE orders (id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

---

7. 加入：

PostgreSQL 執行更嚴格的 SQL 連線標準。確保連接列具有相容的資料類型或使用明確轉換：SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id::TEXT;

---

8. 全文搜尋：

• MySQL： 使用 FULLTEXT 索引。
• PostgreSQL： 利用 TSVECTOR 和 TSQUERY 進行進階全文搜尋。

範例：

• MySQL： SELECT * FROM articles WHERE MATCH(content) AGAINST('search term');
• PostgreSQL： SELECT * FROM articles WHERE content @@ to_tsquery('search & term');

---

9. 外鍵約束：

PostgreSQL 強制執行更嚴格的外鍵約束。驗證架構和資料完整性。

---

10. 標識符區分大小寫：

• MySQL：表格和列名稱通常不區分大小寫（除非使用二進位排序規則）。
• PostgreSQL： 表名和列名在引用時區分大小寫（“table_name”）。

---

11. 預存程序：

預存程序語法有很大不同。

• MySQL： 使用 DELIMITER 定義過程。
• PostgreSQL： 使用 DO 區塊或 CREATE FUNCTION.

---

12. 索引：

PostgreSQL 提供進階索引選項（GIN、GiST、BRIN）並支援函式索引。

---

遷移策略：

1. 利用 pgLoader 或 AWS DMS 等遷移工具進行自動化架構和資料傳輸。
2. 手動檢查和調整 SQL 查詢，特別是涉及區分大小寫、自動增量、全文搜尋、聯結和預存程序的查詢。
3. 最佳化 PostgreSQL 功能的索引。
4. 在生產遷移之前在暫存環境中進行徹底的測試。

徹底了解這些差異可確保從 MySQL 成功且有效率地遷移到 PostgreSQL。

以上是從 MySQL 遷移到 PostgreSQL 關鍵查詢差異和注意事項的詳細內容。更多資訊請關注PHP中文網其他相關文章！