PostgreSQL vs. MySQL

资料地址：https://www.postgresqltutorial.com/postgresql-vs-mysql/

	PostgreSQL	MySQL
Known as	The world’s most advanced open source database.	The world’s most popular open source database.
Development	PostgreSQL is an open source project.	MySQL is an open-source product.
Pronunciation	post gress queue ell	my ess queue ell
Licensing	MIT-style license	GNU General Public License
Implementation programming language	C	C/C++
GUI tool	PgAdmin	MySQL Workbench
ACID	Yes	Yes
Storage engine	Single storage engine	Multiple storage engines e.g., InnoDB and MyISAM
Full-text search	Yes	Yes (Limited)
Drop a temporary table	No TEMP or TEMPORARY keyword in DROP TABLE statement	Support the TEMP or TEMPORARY keyword in the DROP TABLE statement that allows you to remove the temporary table only.
DROP TABLE	Support CASCADE option to drop table’s dependent objects e.g., tables and views.	Does not support CASCADE option.
TRUNCATE TABLE	PostgreSQL TRUNCATE TABLEsupports more features like CASCADE, RESTART IDENTITY, CONTINUE IDENTITY, transaction-safe, etc.	MySQL TRUNCATE TABLE does not support CASCADEand transaction safe i.e,. once data is deleted, it cannot be rolled back.
Auto increment Column	SERIAL	AUTO_INCREMENT
Identity Column	Yes	No
Analytic functions	Yes	No
Data types	Support many advanced types such as array, hstore, and user-defined type.	SQL-standard types
Unsigned integer	No	Yes
Boolean type	Yes	Use TINYINT(1) internally for Boolean
IP address data type	Yes	No
Set default value for a column	Support both constant and function call	Must be a constant or CURRENT_TIMESTAMP for TIMESTAMP or DATETIME columns
CTE	Yes	Yes (Supported CTE since MySQL 8.0)
EXPLAIN output	More detailed	Less detailed
Materialized views	Yes	No
CHECK constraint	Yes	Yes (Supported since MySQL 8.0.16, Before that MySQL just ignored the CHECK constraint)
Table inheritance	Yes	No
Programming languages for stored procedures	Ruby, Perl, Python, TCL, PL/pgSQL, SQL, JavaScript, etc.	SQL:2003 syntax for stored procedures
FULL OUTER JOIN	Yes	No
INTERSECT	Yes	No
EXCEPT	Yes	No
Partial indexes	Yes	No
Bitmap indexes	Yes	No
Expression indexes	Yes	No
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.
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
Partitioning	RANGE, LIST	RANGE, LIST, HASH, KEY, and composite partitioning using a combination of RANGE or LIST with HASH or KEY subpartitions
Task Schedule	pgAgent	Scheduled event
Connection Scalability	Each new connection is an OS process	Each new connection is an OS thread