MySQL、PostgreSQL、Oracle、SQL Server四大数据库对比

一、基础概况

1. SQL Server 只能在Windows上使用，而MySQL、PostgreSQL和Oracle支持跨平台，可以在其他系统上使用，而且可以支持数据库在不同系统之间的移植。
2. MySQL、PostgreSQL免费，SQL Server 和 Oracle 要钱
3. 大小：MySQL< SQL Server < Oracle
4. 并发支持：Oracle > SQL Server > MySQL
5. Oracle不支持自增

二、字段类型

1. VARCHAR vs NVACHAR vs VARCHAR2

   • MySQL和Oracle使用VARCHAR来存储可变长度的字符串，而SQL Server使用NVARCHAR

     SQL Server nvarchar 与 varchar 区别：

     1. nvarchar可以解决存储中文出现乱码的问题

     2. 无需考虑中英文两种字符的差别

        例如：varchar(10) 存10个字母，5个汉字，nvarchar(10) 则存10个字母或10个汉字

   • VARCHAR2是Oracle中的特定数据类型，类似于VARCHAR，但建议在新应用程序中使用VARCHAR2

     VARCHAR2用null代替varchar的空字符串

2. INT vs NUMBER vs INTEGER

   • MySQL和SQL Server使用INT来存储整数值，而Oracle使用NUMBER
   • Oracle还有INTEGER数据类型，它是NUMBER的子类型

3. DATE vs DATETIME vs TIMESTAMP

   • MySQL和SQL Server使用DATETIME来存储日期和时间值，而Oracle使用DATE。

   • Oracle还有TIMESTAMP数据类型，可以存储带有分数秒精度的日期和时间。

     1. MySQL也有TIMESTAMP 不同时区 格式为YYYY-MM-DD HH:MM:SS

     不同时区会查询到不同的值，自动转化

     2. SQL Server的TIMESTAMP 数据类型和日期时间无关

4. DECIMAL vs NUMERIC

   • MySQL和SQL Server使用DECIMAL来存储定点小数，而Oracle使用NUMBER。
   • Oracle的NUMBER数据类型可以存储定点和浮点数。
   • Oracle里NUMERIC是NUMBER的子类型

5. BLOB vs IMAGE vs BFILE

   • MySQL使用BLOB来存储二进制大对象，而SQL Server使用IMAGE
   • Oracle使用BFILE来存储二进制大对象，这些对象在文件系统中以外部方式存储。

6. BOOLEAN

   • MySQL和SQL Server没有本地的BOOLEAN数据类型。相反，它们使用TINYINT或BIT来表示布尔值。（null，0，1）
   • Oracle在Oracle 12c中引入了BOOLEAN数据类型。（null，true，false）
   • PostgreSQL有BOOLEAN类型。（null，true，false）

7. PostgreSQL 没有 MySQL 的各种坑

   MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text… Pg 没有这个限制, text 能支持各种大小.

   按照 SQL 标准, 做 null 判断不能用 = null, 只能用 is null

   the result of any arithmetic comparison with NULL is also NULL

   但 pg 可以设置 transform_null_equals 把 = null 翻译成 is null 避免踩坑

   不少人应该遇到过 MySQL 里需要 utf8mb4 才能显示 emoji 的坑, Pg 就没这个坑.

三、语法区别

1. 返回前10条数据

• MySQL

  select *
  from mytable
  limit 10;
  

• PostgreSQL

  同MySQL

• SQL Server

  select TOP 10 *
  from mytable;
  

• Oracle ORACLE 中ROWNUM用法总结! - IT·达人 - 博客园 (cnblogs.com)

  select *
  from (
      select t.*, ROWNUM rn
      from mytable t
      where ROWNUM <= 10
  )
  where rn > 0
  

2. 分页(返回行6-15)

• MySQL

  第一 select * from mytable limit 5,10
  
  第二 select * from mytable limit 5 offset 10
  

  //为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：    
  mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.   
    
  //如果只给定一个参数，它表示返回最大的记录行数目：    
  mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行   
    
  //换句话说，LIMIT n 等价于 LIMIT 0,n。
  

• PostgreSQL

  select * from mytable limit 5 offset 10
  

• SQL Server

  第一
  select *
  from mytable
  order by id
  offset 5 rows fetch next 10 rows only;
  

  第二
  select * 
  from (
      select *,ROW_NUMBER() OVER (order by id) as rn
      from mytable
  ) as subquery
  where rn between 6 and 15;
  

  第三
  with cte as (
      select *, ROW_NUMBER() over (order by id) as rn
      from mytable
  )
  select *
  from cte
  where rn between 6 and 15;
  

• Oracle

  select * 
  from (
  	select t.*, ROWNUM rn
      from mytable t
      where ROWNUM <= 15
  )
  where rn >= 6;
  

3. 字符串拼接

• MySQL

  select CONCAT('hello',' ','world');
  

• PostgreSQL

  第一
  select 'hello' || ' ' || 'world';
  第二
  CONCAT('hello',' ','world');
  

• SQL Server

  第一
  select 'hello' + ' ' + 'world';
  第二(2012以上版本)
  select CONCAT('hello',' ','world');
  

• Oracle

  第一
  select 'hello' || ' ' || 'world';
  第二(只支持两个字符串拼接,多个需要嵌套CONCAT)
  select CONCAT('hello','world');
  

4. 主键自增

• Oracle

  方式一  序列 + 默认值
  
  - 建序列
  CREATE SEQUENCE seq_name
  [minvalue 1 maxvalue 9223372036854775807[long最大值]  或 NOMAXvalue[不设置最大值]]
  START WITH 1
  INCREMENT BY 1
  NOCACHE;
  
  - 建表
  CREATE TABLE table_name (
      id NUMBER DEFAULT seq_name.NEXTVAL PRIMARY KEY,
      ...
  );
  
  方式二  序列 + 触发器
  
  - 建序列同上
  
  - 建表
  CREATE TABLE table_name (
      id NUMBER,
      ...
  );
  
  - 建触发器
   - 方式一
   CREATE OR REPLACE TRIGGER trigger_name
   BEFORE INSERT ON table_name
   FOR EACH ROW
   BEGIN
       :NEW.id := seq_name.NEXTVAL;
   END;
   
   - 方式二
   CREATE OR REPLACE TRIGGER trigger_name
   BEFORE INSERT ON table_name
   FOR EACH ROW
   BEGIN
       SELECT seq_name.nextval INTO :new.id FROM dual;
   END;
  

• SQL Server

  CREATE TABLE your_table (
      id INT IDENTITY(1,1) PRIMARY KEY,
      column1 VARCHAR(255),
      column2 INT
  );
  

• MySQL

  CREATE TABLE your_table (
      id INT AUTO_INCREMENT PRIMARY KEY,
      column1 VARCHAR(255),
      column2 INT
  );
  

• PostgreSQL

  CREATE TABLE your_table (
      id SERIAL PRIMARY KEY,
      column1 VARCHAR(255),
      column2 INT
  );