国庆期间技术精进06:PostgreSQL vs. MySQL

572 阅读2分钟

当第一次听到PostgreSQL时,对公司选择使用PostgreSQL数据库感到很惊讶,在想公司为什么用它呢?为什么不用mysql呢,那么我们来对比以下两者的不同。

假期马上接近尾声,收收心,慢慢转向学习和工作。

PostgreSQL简述

PostgreSQL vs. MySQL is an important decision when it comes to choosing an open-source relational database management system. Both PostgreSQL and MySQL are time-proven solutions that can compete with enterprise solutions such as Oracle and SQL Server.

MySQL has been famous for its ease of use and speed, while PostgreSQL has many more advanced features, which is the reason that PostgreSQL is often described as an open-source version of Oracle.

The following table compares the features of PostgreSQL vs. MySQL:

特性 Postgresql MySQL
描述 The world’s most advanced open source database The world’s most popular open source database
发展 PostgreSQL is an open source project MySQL is an open-source product
实现语言 C C/C++
图形化工具 PgAdmin MySQL Workbench
ACID Yes Yes
存储引擎 Single storage engine Multiple storage engines e.g., InnoDB and MyISAM
全文检索 Yes Yes
Drop a temporary table No TEMP or TEMPORARY keyword in DROP TABLE statement(随着数据库的连接的断开而被删除) MySQL supports the TEMP or TEMPORARYkeyword in the DROP TABLE statement that allows you to remove the temporary table only.(需要手动删除)
Auto increment Column SERIAL AUTO_INCREMENT
Analytic functions Yes Yes
Data types Support many advanced types such as array, hstore, and user-defined type. SQL-standard types
Boolean type Yes Use TINYINT(1) internally for Boolean
CTE Yes Yes (Supported CTE since MySQL 8.0)
Set default value for a column Support both constant and function call Must be a constant or CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns
CHECK constraint Yes No (MySQL ignores the CHECK constraint)
Table inheritance Yes No
Lv3 1000 4999
Triggers Support triggers that can fire on most types of command, except for ones affecting the database globally e.g., roles and tablespaces. Limited to some commands
Connection Scalability Each new connection is an OS process Each new connection is an OS thread
Covering indexes Yes (since version 9.2) Yes. MySQL supports covering indexes that allow data to be retrieved by scanning the index alone without touching the table data. This is advantageous in case of large tables with millions of rows.
Expression indexes Yes No
Partial indexes Yes No
INTERSECT Yes No

参考资料:www.postgresqltutorial.com/postgresql-…