请选择 进入手机版 | 继续访问电脑版
    查看: 230|回复: 2

    [经验分享] 基于MySQL和SQL Server语法差异总结分析

    [复制链接]

    签到天数: 178 天

    [LV.7]化身百千

    发表于 2018-9-14 17:24:38 | 显示全部楼层 |阅读模式
    因为一早就接触过SQL Server,所以对sql server的语法比较熟悉,后来转学mysql,还有些不适应,所以大致总结了一些mysql和sql server语法不同的地方,其中借鉴了些此文中的内容。

    因为本文是SQL server和mysql语法差异性的总结,所以内容比较杂,没什么逻辑也不详细,还请见谅。在后面的文章中将只说明MySQL的语法。

    1. 和SQL server语法主要不同的地方

    1.1 大小写敏感性
    MySQL和SQL Server一样,对大小写不敏感。但不同的是,在MySQL中对部分对象的引用是大小写敏感的,如数据库名、表名,但对字段、索引、函数、存储过程等的引用不敏感。


    1.2 注释符
    在MySQL中支持三种注释方法:以下都可以是行内注释。

    使用#作为开头,后面的全是注释。
    使用--作为注释开头,但要注意,MySQL中这种注释方法和SQL Server等其他标准数据库注释语法稍有不同,MySQL要求第二个短线后面必须跟一个空白字符,如空格、制表符等。
    使用/**/注释符。

    1.3 自增列
    MySQL中设置自增列(auto_increment)的列必须是有索引的列,且创建表时要显式指定的种子值需要在建表语句之后。另外MySQL一张表只能有一个自增列。而SQL Server中可以有多个自增列。且MySQL中向自增列插入数据时必须使用null来表示插入的是自增列,除非显式指定插入列表中不包含自增列,而SQL Server向自增列插入数据时可以且必须无视该列,除非设置显示插入模式。

    -- SQL Server直接使用identity,但必须有非自增列之外的列才能插入,除非显式开启手动插入自增列
    create table emp1(id int not null identity(1,2),name CHAR(20));
    insert into emp1 VALUES('malongshuai');
    insert into emp1 values('gaoxiaofang');
    select * from emp1;

    -- MySQL中自增列必须为索引列,并且只能设置种子值而不能直接设置步长
    create table emp1(id int not null primary key AUTO_INCREMENT);
    create table emp2(id int not null primary key AUTO_INCREMENT) auto_increment=100;
    insert into emp1 values(null);
    insert into emp2 values(null);
    设置自增列的步长,分为全局级别和会话级别。但它们都是临时生效的,重启实例后效果就消失,要永久生效可以将其写入配置文件中。如果是会话级别,那么当用户新建一个会话的时候,那么步长又回到了全局级别。

    mysql不能设置为表级别的步长!!

    设置和查看全局和会话级别的变量时,分别使用如下语句:

    set [session] auto_increment_increment=100;  -- 会话级的步长设置
    set global auto_increment_offset=12;         -- 全局级的种子值
    show [session] variables like 'auto_inc%';
    show global variables like 'auto_inc%';
    这两个变量都有session级和global级。其中auto_increment_offset项为起始计算项,auto_increment_increment项为步长项。它们的处理模式和SQL server的处理方式相差甚远。当同时设置了这两个变量时,如果offsert设置的值大于increment的值,则offset将被忽略,且MySQL会以"offset+N\*increment"计算下一条插入的记录值。例如,"offset=3、increment=5",当前表的最后一个自增列值为13,则下一条插入的自增值为18,因为"offset+N\*increment"将计算得到[3,8,13,18,23,28...]序列,所以从序列中挑出大于且最接近当前最后一个值13的项,即18。

    以上言论为官方手册上的解释(原文:the next value inserted is the least value in the series that is greater than the maximum existing value in the AUTO_INCREMENT column),但实际上并不标准,更准确的说法是:根据当前offset和increment计算增长序列,并从中挑出大于或等于原序列的下一个值。例如上面offset=3,生成的序列为[3,8,13,18,23...],下一个要插入的值为18,但插入之前如果将offset改为4,则新的序列为[4,9,14,19,24],那么它将插入19,而不是14,尽管14大于当前最后一个记录值13。同理,如果将offset改小,例如设置为2,则序列为[2,7,12,17,22],那么下一个插入的值将是22。同理,修改increment也是一样计算的。

    问:如果有一张表,里面有个字段为id的自增主键,当已经向表里面插入了10条数据之后,删除了id为8、9、10的数据,再把mysql重启,之后再插入一条数据,那么这条数据的id值应该是多少,是8还是11?

    答:是11。但是在老版本中,innodb存储引擎的表会是8,这是innodb的bug,在后来修复了,只是在mysql5.6中没有了。更简单地说,在未修复之前,auto_increment的值来自于内存中的自增计数器,当停止服务后,内存中的计数器就消失了,在重启时,auto_increment的值会根据表中已有的值进行初始化。当修复该功能之后,auto_increment计数器的值会持久化。对于MariaDB而言,则是从MariaDB 10.2.4开始持久化的。

    查看当前自增值的方法:

    show table status like "table_name_string";   -- 查看某个表的下一个自增值
    select last_insert_id();   -- 查看当前环境下最后一次自增列的插入值
    关于"last_insert_id"函数,在下一篇文章"内置函数"中再做介绍。


    1.4 查看表的属性
    -- SQL Server使用存储过程sp_help
    exec sp_help emp;


    -- MySQL使用desc描述或者使用show
    mysql> desc emp1;
    +-------+---------+------+-----+---------+----------------+
    | Field | Type    | Null | Key | Default | Extra          |
    +-------+---------+------+-----+---------+----------------+
    | id    | int(11) | NO   | PRI | NULL    | auto_increment |
    +-------+---------+------+-----+---------+----------------+

    mysql> show table status like 'emp1'\G
    *************************** 1. row ***************************
               Name: emp1
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 1
    Avg_row_length: 16384
        Data_length: 16384
    Max_data_length: 0
       Index_length: 0
          Data_free: 0
    Auto_increment: 2
        Create_time: 2017-03-22 10:05:49
        Update_time: NULL
         Check_time: NULL
          Collation: latin1_swedish_ci
           Checksum: NULL
    Create_options:
            Comment:
    1 row in set (0.00 sec)

    1.5 修改表名
    -- SQL Server使用存储过程sp_rename
    EXEC sp_rename emp,emp2 [object]

    -- mysql使用alter语句中的rename功能
    alter table emp rename [to] emp3;

    1.6 删除表
    在删除表方面,MySQL比SQL Server要方便很多,判断起来也方便很多。

    -- SQL Server删除表,每次只能删除一张表
    if object_id('table_name') is not null drop table table_name;
    if exists(select object_id('table_name')) drop table table_name;

    -- MySQL可以直接判断,且一次可以删除多表
    drop table if exists table_name1,table_name2...

    1.7 修改字段属性
    -- SQL Server只能修改字段属性(数据类型、空性),不能修改约束类属性,
    -- 约束类属性需要使用"alter table … add constraint"
    alter table emp2 alter column id int not null;
    alter table emp2 add gender char(2);
    alter table emp2 add CONSTRAINT def_key DEFAULT('男') FOR gender;
    alter table emp2 add constraint pk_key primary key clustered(id);

    -- mysql修改字段属性有几种方法
    alter table table_name
    | ALTER [COLUMN] col_name {SET DEFAULT string | DROP DEFAULT}
    | CHANGE [COLUMN] old_col_name new_col_name column_definition
          [FIRST|AFTER col_name]
    | MODIFY [COLUMN] col_name column_definition
          [FIRST | AFTER col_name]
    也就是说,change和modify都可以修改列的定义,包括约束类的属性、字段的位置,且change比modify更多一个重命名列的功能。而alter column则只能设置默认值和删除默认值。注意,重命名和修改字段时,需要重新定义字段属性。


    1.8 添加、删除字段和约束
    首先需要说明的是,MySQL中任何存储引擎都不支持check约束,官方手册上说明了会对check语句进行读取检查,但是不会生效,也就是即使是对的check约束也是被忽略的。要在MySQL中实现check约束可以考虑使用触发器或者通过数据类型来限制。

    -- MySQL中添加、删除字段和约束的语法

    -- 添加字段
    | ADD [COLUMN] col_name column_definition
          [FIRST | AFTER col_name ]
    | ADD [COLUMN] (col_name column_definition,...)  -- 可以一次性添加多个字段
    -- 删除字段
    | DROP [COLUMN] col_name
    -- 添加约束
    | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
    | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
          reference_definition
    | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name]
          [index_type] (index_col_name,...) [index_option] ...
    -- 删除约束
    | DROP PRIMARY KEY
    | DROP FOREIGN KEY fk_symbol
    | DROP {INDEX|KEY} index_name
    | DISABLE KEYS
    | ENABLE KEYS
    -- 添加和删除默认值约束
    | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
    对于MariaDB,从10.2.1开始,其支持DROP CONSTRAINT子句,并引入了DEFAULT约束,还支持check约束。且在MariaDB 10.0.2版本之后,操作字段时甚至支持if exists和if not exists。包括如下情况:

    ADD COLUMN       [IF NOT EXISTS]
    ADD INDEX        [IF NOT EXISTS]
    ADD FOREIGN KEY  [IF NOT EXISTS]
    ADD PARTITION    [IF NOT EXISTS]
    CREATE INDEX     [IF NOT EXISTS]

    DROP COLUMN      [IF EXISTS]
    DROP INDEX       [IF EXISTS]
    DROP FOREIGN KEY [IF EXISTS]
    DROP PARTITION   [IF EXISTS]
    CHANGE COLUMN    [IF EXISTS]
    MODIFY COLUMN    [IF EXISTS]
    DROP INDEX       [IF EXISTS]
    对于SQL Server,不管是什么约束,都能使用下面的语句进行删除,但MySQL有些麻烦,见下文具体说明:

    alter table table_name drop constraint constraint_name
    添加/删除字段

    -- SQL Server添加字段,只能一个字段一个字段添加
    alter table emp add name char(20)
    -- MySQL添加字段,可以一次添加一个字段,也可以一次添加多个字段
    ALTER TABLE emp ADD NAME CHAR(20) NOT NULL;
    ALTER TABLE emp ADD (gender CHAR(6) NOT NULL DEFAULT 'male',phone CHAR(11));
    添加/删除主键、外键约束

    /*添加主键约束*/
    -- SQL Server
    alter table emp10 add constraint pk_id primary key clustered(id);
    -- MySQL添加主键
    ALTER TABLE emp10 ADD CONSTRAINT pk_id PRIMARY KEY (id);
    -- 添加外键约束,SQL Server和MySQL相同
    ALTER TABLE emp10 ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES emp20(id);
    -- MySQL删除主键、外键(需要先删除外键)
    alter table emp10 drop foreign key fk_id;
    alter table emp10 drop primary key;
    MySQL在外键上和SQL Server以及Oracle都不同,MySQL在创建外键的时候,会自动在外键列上创建一个索引,且这个索引无法人为删除。在表联接的过程中因为会依赖性的对外表加上锁,如果外键列上没有索引,可能会加上表锁降低并发且容易导致死锁,如果有索引,将会进行范围锁定,增强并发性也减少了死锁的出现几率。在这一点上,MySQL比SQL Server做的要好。

    添加唯一性约束

    --SQL Server
    alter table emp10 add constraint uni_name unique nonclustered(name);
    -- MySQL添加唯一性约束
    ALTER TABLE emp10 ADD CONSTRAINT uni_name UNIQUE KEY(`name`);
    -- MySQL删除唯一性约束
    alter table emp10 drop key uni_name
    默认值的设置方法
    SQL Server的默认值约束和MySQL的默认值约束设置方法相差很大,MySQL的默认值约束不能使用constraint来设置,只能通过修改列属性来设置。另外,MySQL的default关键字后是不能加括号的,而SQL Server是无所谓的。

    -- SQL Server设置默认值时可有可没有括号
    create table emp10(name int not null default(12));
    create table emp10(name int not null default 12);
    alter table emp10 add constraint def_name default 12 for name;
    -- MySQL设置默认值时不能使用括号
    create table emp(id int not null default 12);
    alter table test.emp alter id set default 12;  /*使用change和modify也行,但是要重定义列属性*/
    -- MySQL删除默认值约束
    alter table test.emp alter id drop default;

    1.9 创建表模板
    -- SQL Server
    select * into table_name1 from table_name2;             /*复制表结构和数据*/
    select * into table_name1 from table_name2 where 1=0;   /*只复制表结构*/
    -- MySQL
    create table table_name1 like table_name2;              /*只复制表结构*/
    create tbale table_name1 as select * from table_name2;  /*复制表结构和数据*/
    MySQL中复制表结构时不会复制主键、索引、自增列等任何属性,仅仅只是简单的建立一张表然后插入数据。但SQL Server复制表结构时会复制自增列属性。


    1.10 MySQL中的字段显示宽度和zerofill
    在MySQL中可以给整数数据类型指定结果的显式宽度,如int(4)表示将显示4位整数,如果实际值的位数小于显示值宽度,则使用空格填充。而结果位数超出时将不影响显示结果。一般该功能都会配合zerofill属性用0代替空格填充,但是使用了zerofill后,该列就会自动变成无符号字段。

    zerofill属性的声明必须紧跟在整数数据类型的后面,而不能跟在如not null这样的属性后面。

    SQL Server中没有该功能。

    要注意的是显示宽度和数据类型限制的字段宽度是不一样的。显示宽度不会影响字段的限制宽度,只是起一个显示作用。

    CREATE TABLE test3(id INT(2) ZEROFILL NOT NULL);
    ALTER TABLE test3 MODIFY id INT(2) ZEROFILL NOT NULL;
    ALTER TABLE test3 CHANGE id id INT(2) ZEROFILL NOT NULL;
    INSERT INTO test3 VALUES(1),(2),(11),(111);
    SELECT id FROM test3;
    +-----+
    | id  |
    +-----+
    |  01 |
    |  02 |
    |  11 |
    | 111 |
    +-----+
    4 rows in set (0.00 sec)

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    返回顶部