相关推荐recommended
MySQL、PostgreSQL、Oracle、SQL Server四大数据库对比
作者:mmseoamin日期:2023-12-21

一、基础概况

  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、

    • INT vs NUMBER vs INTEGER

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

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

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

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

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

          1. SQL Server的TIMESTAMP 数据类型和日期时间无关
        • DECIMAL vs NUMERIC

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

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

              • MySQL和SQL Server没有本地的BOOLEAN数据类型。相反,它们使用TINYINT或BIT来表示布尔值。(null,0,1)
              • Oracle在Oracle 12c中引入了BOOLEAN数据类型。(null,true,false)
              • PostgreSQL有BOOLEAN类型。(null,true,false)
              • 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
          );