sqlite、mysql和postgresql對比

關系型資料庫的使用已經有相當長的時間了。它們變得流行起來托了管理系統的福，關系模型被實作得相當的好，并且被證明是操作資料的好方法（特别是事務性強的應用）。

在這篇DigitalOcean文章中，我們将嘗試了解一些最常用、最流行的關系型資料庫管理系統（RDBMS）的核心差別。我們将會探索最底層的差別——特性與功能，它們如何工作，在哪方面更出色，以幫助程式員選擇合适的RDBMS。

目錄：

一、資料庫管理系統

1、關系型資料庫管理系統

2、關系與資料類型

3、重要的和流行的關系型資料庫

二、SQLite

1、SQLite支援的資料類型

2、SQLite的優勢

3、SQLite的劣勢

4、何時使用SQLite

5、何時不用SQLite

三、MySQL

1、MySQL支援的資料類型

2、MySQL的優勢

3、MySQL的劣勢

4、何時使用MySQL

5、何時不用MySQL

四、PostgreSQL

1、PostgreSQL支援的資料類型

2、PostgreSQL的優勢

3、PostgreSQL的劣勢

4、何時使用PostgreSQL

5、何時不用PostgreSQL

一、資料庫管理系統

資料庫是有組織地存儲模型資料的空間，存儲各種類型的資訊（資料）。每個資料庫，除了無模式型的，都有一個模型，提供資料的結構描述。資料庫管理系統是管理資料庫結構、大小和排序的應用（或庫）。

1、關系型資料庫管理系統

關系型資料庫系統實作了關系模型，并用它來處理資料。關系模型在表中将資訊與字段關聯起來（也就是schemas），進而存儲資料。

這種資料庫管理系統需要結構（例如表）在存儲資料之前被定義出來。有了表，每一列（字段）都存儲一個不同類型（資料類型）的資訊。資料庫中的每個記錄，都有自己唯一的key，作為屬于某一表的一行，行中的每一個資訊都對應了表中的一列——所有的關系一起，構成了關系模型。

2、關系和資料類型

關系可以被看做是包含一系列共同表示被保持資料庫以及相關資訊的屬性的數學集合. 這種類型的識别和采集方法可以讓關系型資料庫以它們自己的方式運作.

在定義一個可以向其中插入資料的表時，每一個形成一條記錄的元素（例如： 屬性）都必須同定義的資料類型相比對(例如：一個integer, 一個date 等等.). 不同的關系型資料庫管理系統實作了不同的資料類型 -- 它們不總是能直接互相轉換的.

與限制的協作，就像我們之前已經介紹過的，在關系資料庫的使用中是很普遍的。事實上，限制形成了關系的核心.

3、重要和流行的關系型資料庫

本文中，我們将會介紹三種主要而且重要的開源關系型資料庫管理系統，是他們影響了應用開發世界。

SQLite	一個強大的嵌入式關系型資料庫管理系統
MySQL	最流行的RDBMS
PostgreSQL	最先進SQL型開源objective-RDBMS

注: 開源應用總是可以自由使用的。大多數時候，複制工程（利用代碼）建立新應用也是被允許的。如果你對DBMS感興趣，你可以看看一些基于這些工程的分支項目，例如MariaDB。

二、SQLite

SQLite是非凡的資料庫，他可以程序在使用它的應用中。作為一個自包含、基于檔案的資料庫，SQLite提供了出色的工具集，可以處理所有類型的資料，沒有什麼限制，而且比起伺服器運作的程序型伺服器使用起來輕松許多。

一個應用使用SQLite時，它的功能直接被內建在其中，應用會直接通路包含資料的檔案（即SQLite資料庫）,而不是通過一些端口（port, socket）來互動。感謝這種底層技術，這使SQLite變得非常快速和高效，并且十分強大。

1、SQLite支援的資料類型

NULL	NULL值
INTEGER	有符号整數，按照設定用1、2、3、4、6或8位元組存儲
REAL	浮點數，使用8位元組IEEE浮點數方式存儲
TEXT	文本字元串，使用資料庫編碼存儲（UTF-8, UTF-16BE 或 UTF-16LE）
BLOB	二進制大對象，怎麼輸入就怎麼存儲

2、SQLite 的優點

基于檔案	整個資料庫都包含在磁盤上的一個檔案中，是以它有很好的遷移性
标準化	盡管它看起來像個“簡化版”的資料庫，SQLite 确實支援 SQL。它略去了一些功能(RIGHT OUTER JOIN 和 FOR EACH STATEMENT)，但是，又同時增加了一些其他功能
對開發乃至測試都很棒	在絕大多數應用的開發階段中，大部分人都非常需要解決方案能有并發的靈活性。SQLite 含有豐富功能基礎，所能提供的超乎開發所需，并且簡潔到隻需一個檔案和一個 C 連結庫

3、SQLite的缺點

沒有使用者管理	進階資料庫都能支援使用者系統，例如，能管理資料庫連接配接對資料庫和表的通路權限。但由于 SQLite 産生的目的和本身性質（沒有多使用者并發的高層設計），它沒有這個功能
缺乏額外優化性能的靈活性	仍然是從設計之初，SQLite 就不支援使用各種技巧來進行額外的性能優化。這個庫容易配置，容易使用。既然它并不複雜，理論上就無法讓它比現在更快，其實作在它已經很快了

4、什麼時候要用 SQLite

嵌入式應用	所有需要遷移性，不需要擴充的應用，例如，單使用者的本地應用，移動應用和遊戲
代替磁盤通路	在很多情況下，需要頻繁直接讀/寫磁盤檔案的應用，都很适合轉為使用 SQLite ，可以得益于 SQLite 使用 SQL 帶來的功能性和簡潔性
測試	它能秒殺大部分專門針對應用業務邏輯（也就是應用的主要目的：能完成功能）的測試

5、什麼時候不要用SQLite

多使用者應用	如果你在開發的應用需要被多使用者通路，而且這些使用者都用同一個資料庫，那麼相比 SQLite 最好還是選擇一個功能完整的關系型資料庫（例如 MySQL）
需要大面積寫入資料的應用	SQLite 的缺陷之一是它的寫入操作。這個資料庫同一時間隻允許一個寫操作，是以吞吐量有限

三、MySQL

MySQL 在所有大型資料庫伺服器中最流行的一個. 它的特性豐富，産品的開源性質使得其驅動了線上大量的網站和應用程式. 要入手 MySQL 相對簡單，開發人員可以在網際網路上面通路到大量有關這個資料庫的資訊.

注意: 由于這個産品的普及性，大量的第三方應用、工具和內建庫對于操作這個RDBCMS的方方面面大有幫助.

Mysql沒有嘗試去實作SQL标準的全部，而是為使用者提供了很多有用的功能. 作為一個獨立的資料庫伺服器，應用程式同Mysql守護程序的互動，告訴它去通路資料庫自身 -- 這一點不像 SQLite.

1、MySQL支援的資料類型

TINYINT	一個非常小的整數
SMALLINT	一個小整數
MEDIUMINT	一個中間大小的整數
INT or INTEGER	一個正常大小的整數
BIGINT	一個大的整數
FLOAT	一個小的 (單精度) 浮點數，不能是無符号的那種
DOUBLE, DOUBLE PRECISION, REAL	一個正常大小 (雙精度) 的浮點數，不能使無符号的那種
DECIMAL, NUMERIC	沒有被包裝的浮點數。不能使無符号的那種
DATE	一個日期
DATETIME	一個日期和時間的組合
TIMESTAMP	一個時間戳
TIME	一個時間
YEAR	一個用兩位或者4位數字格式表示的年份(預設是4位)
CHAR	一個固定長度的字元串，存儲時總是在其固定長度的空間裡右對齊
VARCHAR	一個可變長度的字元串
TINYBLOB, TINYTEXT	一個BLOB或者TEXT列，最大長度255 (2^8 - 1)個字元
BLOB, TEXT	一個BLOB或者TEXT列，最大長度 65535 (2^16 - 1)個字元
MEDIUMBLOB, MEDIUMTEXT	一個BLOB或者TEXT列，最大長度 16777215 (2^24 - 1)個字元
LONGBLOB, LONGTEXT	一個BLOB或者TEXT列，最大長度4294967295 (2^32 - 1) 個字元
ENUM	一個枚舉類型
SET	一個集合

2、MySQL的優點

容易使用	安裝MySQL非常容易。第三方庫，包括可視化（也就是有GUI）的庫讓上手使用資料庫非常簡單
功能豐富	MySQL 支援大部分關系型資料庫應該有的 SQL 功能——有些直接支援，有些間接支援
安全	MYSQL 有很多安全特性，其中有些相當進階
靈活而強大	MySQL 能處理很多資料，此外如有需要，它還能“适應”各種規模的資料
快速	放棄支援某些标準，讓 MySQL 效率更高并能使用捷徑，是以帶來速度的提升

3、MySQL的缺點

已知的局限	從設計之初，MySQL 就沒打算做到全知全能，是以它有一些功能局限，無法滿足某些頂尖水準應用的需求
可靠性問題	MySQL 對于某些功能的實作方式(例如，引用，事務，資料稽核等) 使得它比其他一些關系型資料庫略少了一些可靠性
開發停滞	盡管 MySQL 理論上仍是開源産品，也有人抱怨它誕生之後更新緩慢。然而，應該注意到有一些基于 MySQL 并完整內建的資料庫（如 MariaDB），在标準的 MySQL 基礎上帶來了額外價值

4、何時使用 MySQL？

分布式操作	當你需要的比SQLite可以提供的更多時，把MySQL包括進你的部署棧，就像任何一個獨立的資料庫伺服器，會帶來大量的操作自由和一些先進的功能
高安全性	MySQL的安全功能，用一種簡單的方式為資料通路（和使用）提供了可靠的保護
Web網站 和 Web應用	絕大多數的網站（和Web應用程式）可以忽視限制性地簡單工作在MySQL上。這種靈活的和可擴充的工具是易于使用和易于管理的——這被證明非常有助于長期運作
定制解決方案	如果你工作在一個高度量身定制的解決方案上，MySQL能夠很容易地尾随和執行你的規則，這要感謝其豐富的配置設定和操作模式

5、何時不用 MySQL？

SQL 服從性	因為 MySQL 沒有[想要]實作 SQL 的全部标準，是以這個工具不完全符合SQL。如果你需要對這樣的關系資料庫管理系統進行整合，從MySQL進行切換是不容易的
并發	即使MySQL和一些存儲引擎能夠真地很好執行讀取操作，但并發讀寫還是有問題的
缺乏特色	再次提及，根據資料庫引擎的選擇标準，MySQL會缺乏一定的特性，如全文搜尋

四、PostgreSQL

PostgreSQL 是一個先進的，開放源代碼的[對象]-關系型資料庫管理系統，它的主要目标是實作标準和可擴充性. PostgreSQL, 或者說是 Postgres, 試圖把對 ANSI/ISO SQL标準的采用與修正結合起來.

對比其他的RDBMS, PostgreSQL以它對于對象-關系和或關系型資料庫功能，比如對于可靠事務，例如原子性，一緻性，隔離性和持久性（ACID）的完全支援，這些東西的高度需求和集合的支援，以示其獨特性.

由于強大的底層技術, Postgres對于高效的完成許多處理任務很有一手. 得益于其多版本并發控制 (MVCC)的實作，在沒有讀取鎖的前提下也能達成并發, 這也同樣確定了ACID的實施.

PostgreSQL是高度可程式設計的, 因而可以使用被稱作“存儲過程”的自定義程式進行擴充. 這些功能可以被建立用來簡化一個寫重複、複雜并且常常需要資料庫操作的任務的執行.

雖然特性強大，但這個 DBMS并沒有MySQL那麼流行, 可還是有許多迷人的第三方工具和庫被設計出來用于使得對PostgreSQL的操作簡化. 如今通過許多作業系統預設的包管理器輕松的擷取PostgreSQL已成為可能.

1、PostgreSQL支援的資料類型

bigint	有符号的八位整數
bigserial	自增長的八位整數
bit [(n)]	固定長度的位串
bit varying [(n)]	可變長度的位串
boolean	邏輯布爾值(true/false)
box	在一個平面上的矩形框
bytea	二進制資料("位數組")
character varying [(n)]	可變長度的字元串
character [(n)]	固定長度的字元串
cidr	IPv4 或者 IPv6 網絡位址
circle	平面上的一個圓
date	月曆日期 ( 年月日)
double precision	雙精度浮點數（8位）
inet	IPv4 或者 IPv6 主機位址
integer	有符号的四位整數
interval [fields] [(p)]	時間跨度
line	平面上的一個無限長的直線
lseg	平面上的一個線段
macaddr	MAC (媒體通路控制)位址
money	貨币金額
numeric [(p, s)]	可選精度的精确數字
path	一個平面上的幾何路徑
point	一個平面上的幾何點
polygon	一個平面上的閉合的幾何路徑
real	單精度浮點數(4 位)
smallint	有符号的兩位整數
serial	自增長4位整數
text	可變長度字元創
time [(p)] [without time zone]	一天中的時間(無時區)
time [(p)] with time zone	一天中的時間，包含時區
timestamp [(p)] [without time zone]	日期和時間（沒有時區）
timestamp [(p)] with time zone	日期和時間，包含時區
tsquery	文本搜尋查詢
tsvector	文本搜尋文檔
txid_snapshot	使用者級事務ID快照
uuid	通用的唯一辨別符
xml	XML 資料

2、PostgreSQL的優點

标準支援 SQL 的開源關系型資料庫	PostgreSQL 是一個開源的，免費的，同時非常強大的關系型資料管理系統
強大的社群	PostgreSQL 背後有熱忱而經驗豐富的社群，可以通過知識庫和問答網站擷取支援，全天候免費
強大的第三方支援	即使其本身功能十分強大，PostgreSQL 仍附帶有許多強大的開源第三方工具來輔助系統的設計、管理和使用
可擴充性	可以用預先存儲的流程來程式性擴充 PostgreSQL ，一個進階的關系型資料庫理應如此
面向對象	PostgreSQL 不隻是一個關系型資料庫，還是一個面向對象資料庫——支援嵌套，及一些其他功能

3、PostgreSQL的缺點：

性能	對于簡單而繁重的讀取操作, 超過了 PostgreSQL 的殺傷力，可能會出現比同行（如MySQL）更低的性能
普及	按給出的該工具的性質，從普及度來說它還缺乏足夠背景支撐，盡管有大量的部署——這可能會影響能夠獲得支援的容易程度
托管	由于上述因素的影響，要讓主機或服務提供商提出使用PostgreSQL執行個體是很難的

4、何時使用PostgreSQL？

資料完整性	當可靠性和資料完整性是絕對必要而無需理由時，PostgreSQL是更好的選擇
複雜的自定義過程	如果你需要你的資料庫執行自定義過程，可擴充的PostgreSQL是更好的選擇
整合	在将來，如果可能要把整個資料庫系統遷移到另一個适當的解決方案（例如Oracle）中，PostgreSQL對于這種切換将是最相容和易于操作的
複雜的設計	相比其他的開源和免費的 RDBMS（關系資料庫管理系統）實作來說，對于複雜的資料庫設計，PostgreSQL提供了大部分的功能和可能性，同時并沒放棄其他有價值的地方

5、何時不用 PostgreSQL？

速度	如果你需要的隻是快速的讀取操作, PostgreSQL 不是為此而準備的工具
簡化體制	除非你需要絕對的資料完整性，原子性，一緻性，隔離性，耐久性，或複雜的設計，PostgreSQL 對簡化體制來說是殺手
複制	除非你願意花不少時間，精力和資源，否則對于那些缺乏資料庫和系統管理經驗的人來說，實作與MySQL的（主從）複制可能不容易