当第一次听到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 |