骏马金龙 (新博客：www.junmajinlong.com)

网名骏马金龙，钟情于IT世界里的各种原理和实现机制，强迫症重症患者。爱研究、爱翻译、爱分享。特借此一亩三分田记录自己成长点滴！！！

MySQL基本语法(一)：和SQL Server语法的差异小归纳

因为一早就接触过SQL Server，所以对sql server的语法比较熟悉，后来转学mysql，还有些不适应，所以大致总结了一些mysql和sql server语法不同的地方，其中借鉴了些此文中的内容。

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

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

1.1 大小写敏感性

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

1.2 注释符

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

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

1.3 自增列

MySQL中设置自增列(auto_increment)的列必须是有索引的列，且创建表时要显式指定的种子值需要在建表语句之后。另外MySQL一张表只能有一个自增列。且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

  1. 添加/删除字段

    -- 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));

  2. 添加/删除主键、外键约束

    /*添加主键约束*/
-- 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做的要好。

  3. 添加唯一性约束

    --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

  4. 默认值的设置方法
    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)

2. 数据类型

数据类型的范围是根据bit位的数量值来计算的。4字节的int占用32bit，所以可以表示的范围为0-2^32。

数值在存储(或调入内存)时，以数值型方式存储比字符型或日期时间类型更节省空间。在整数值存储上，0-255之间的任意整数都只占一个字节，256-65535之间的任意整数都占2个字节，而占用4个字节时便可以代表几十亿个整数之间的任意一个，这显然比字符型存储时每个字符占用一个字节节省空间的多。例如值"100"存储为字符型时占用三个字节，而存储为数值型将只占用一个字节。因此数据库默认将不使用引号包围的值当作数值型，如果明确要存储为字符型或日期时间型则应该使用引号包围以避免歧义。

值          CHAR(4)    存储需求    VARCHAR(4)    存储需求
-----------------------------------------------------------
''          '    '     4个字节     ''            1个字节
'ab'        'ab  '     4个字节     'ab '         3个字节
'abcd'      'abcd'     4个字节     'abcd'        5个字节
'abcdefgh'  'abcd'     4个字节     'abcd'        5个字节

MySQL在检索或操作char时会删除尾随空格，也就是说在where语句中name='gaoxiaofang　　　'和name='gaoxiaofang'的结果是一样的；若name='gaoxiaofang　　　'，那么concat(name,'x')的结果将是gaoxiaofangx。

而检索或操作varchar时不会删除尾随空格。但是char类型的列和varchar类型的列进行比较会忽略尾随空格，即 char:a=varchar:a。

mysql> create table test(a char(10),b varchar(10));
mysql> insert into test select 'a   ','a   ';
mysql> select concat(a,'x'),concat(b,'x'),a=b from test;
+---------------+---------------+-----+
| concat(a,'x') | concat(b,'x') | a=b |
+---------------+---------------+-----+
| ax            | a   x         |   1 |
+---------------+---------------+-----+
1 row in set

关于char(M)和varchar(M)，其长度是M个字符（MySQL早期版本是M字节），其字节数和字符集有关，例如latain1字符集下char(30)表示能存储30个字符也就是30个字节，而utf8字符集下char(30)只能存储30个字符(哪怕是英文字母)，但该列将占用30*3=90个字节的空间。

mysql> create table test9(a char(2) charset utf8mb4,b char(2)) charset=latain1;
mysql> insert into test9 values('我是','wo'),('wo','wo');
mysql> select length(a),char_length(a),length(b),char_length(b) from test9;
+-----------+----------------+-----------+----------------+
| length(a) | char_length(a) | length(b) | char_length(b) |
+-----------+----------------+-----------+----------------+
|         6 |              2 |         2 |              2 |
|         2 |              2 |         2 |              2 |
+-----------+----------------+-----------+----------------+
2 rows in set (0.00 sec)'

varchar(M)的字节数还和存储的字节数有关，每2^8次方字节增加一字节结束符。

关于日期时间的输入方式是非常宽松的，以下几种方式都是被允许的：任意允许的分隔符，建议使用4位的年份。

2011-01-01 18:40:20
2011/01/01 18-40-20
20110101184020

对于ENUM，插入数据时忽略大小写。如果enum列是允许NULL的，则NULL值也是有效值。

对于SET类型，和enum类似，不区分大小写，存储时删除尾随空格，null也是有效值。但不同的是可以组合多个给出的值。如set('a','b','c','d')可以存储'a,b','d,b'等，多个成员之间使用逗号隔开。所以，使用多个成员的时候，成员本身的值中不能出现逗号。并且存储数据时忽略重复成员并按照枚举时的顺序存储，如set('d','b','a')，存储'a,b,a','b,a,b'的结果都是'b,a'。使用find_in_set(set_value,set_column_name)可以检索出包含指定set值set_value的行。

SELECT * FROM test6 WHERE FIND_IN_SET('d',col)>0;

3. 数据类型属性

3.1 unsigned

unsigned属性就是让数值类型的数据变得无符号化。使用unsigned属性将会改变数值数据类型的范围，例如tinyint类型带符号的范围是-128到127，而使用unsigned时范围将变成0到255。同时unsigned也会限制该列不能插入负数值。

create table t(a int unsigned,b int unsigned);
insert into t select 1,2;
insert into t select -1,-2;

上面的语句中，在执行第二条语句准备插入负数时将会报错，提示超出范围。

使用unsigned在某些情况下确有其作用，例如一般的ID主键列不会允许使用负数，它相当于实现了一个check约束。

但是使用unsigned有时候也会出现些不可预料的问题：在进行数值运算时如果得到负数将会报错。例如上面的表t中，字段a和b都是无符号的列，且有一行"a=1,b=2"。

mysql> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set

此时如果计算"a-b"将会出错，不仅如此，只要是unsigned列参与计算并将得到负数都会出错。

mysql> select a-b from t;
1690 - BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'
mysql> select a-2 from t;
1690 - BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - 2)'

而不是负数的结果将不会有影响。

mysql> select 2-a,a*3 from t;
+-----+-----+
| 2-a | a*3 |
+-----+-----+
|   1 |   3 |
+-----+-----+
1 row in set

这并不是MySQL中的bug，在C语言中的unsigned也一样有类似的问题。这个问题在MySQL中设置set sql_mode='no_unsigned_subtraction'即可解决。

3.2 zerofill

zerofill修饰字段后，不足字段显示部分将使用0来代替空格填充，启用zerofill后将自动设置unsigned。zerofill一般只在设置了列的显示宽度后一起使用。关于列的显示宽度在上文已经介绍过了。

mysql> create table t1(id int(4) zerofill);
mysql> select * from t1;
+-------+
| id    |
+-------+
|  0001 |
|  0002 |
|  0011 |
| 83838 |
+-------+
4 rows in set (0.00 sec)

zerofill只是修饰显示结果，不会改变存储的数据值。

4. 操作符

只说明些SQL Server中没有的运算符。详细内容见官方手册：函数和操作符。

4.1 安全等于运算符(<=>)

这个符号和"="进行相同的运算，但是它多出的一个功能是可以和NULL进行比较。

当比较的两边都是NULL时返回1而不是NULL，只有一边是null时返回0而不是null，其余的时候和"="的结果一样。

mysql> SELECT 1<=>NULL UNION ALL SELECT NULL<=>NULL UNION ALL SELECT 1=0 UNION ALL SELECT 1<=>0;
+----------+
| 1<=>NULL |
+----------+
|        0 |
|        1 |
|        0 |
|        0 |
+----------+
4 rows in set

4.2 正则表达式运算符(regexp或者rlike)

在SQL Server中没有正则表达式运算符，而MySQL中有。格式：expression regexp reg_pattern

若expression满足reg_pattern，则返回1，无法匹配成功则返回0。若expression或reg_pattern任意一方为null，则返回null。

MySQL实现的是扩展正则表达式。

mysql> SELECT 'basskd' REGEXP '^b','basskd' REGEXP 's.k','basskd' REGEXP NULL,NULL REGEXP '^b';
+----------------------+-----------------------+----------------------+------------------+
| 'basskd' REGEXP '^b' | 'basskd' REGEXP 's.k' | 'basskd' REGEXP NULL | NULL REGEXP '^b' |
+----------------------+-----------------------+----------------------+------------------+
|                    1 |                     1 | NULL                 | NULL             |
+----------------------+-----------------------+----------------------+------------------+
1 row in set

4.3 连接操作符

在MySQL中，"+"不算是一种连接操作符。但是它的表达式是另有意义的：它会将两边的表达式尝试转换为数值型进行数值相加运算，如果转换失败的则表示为0。

mysql> select '12'+'34',12+'34','abc'+12,'a'+'abc';
+-----------+---------+----------+-----------+
| '12'+'34' | 12+'34' | 'abc'+12 | 'a'+'abc' |
+-----------+---------+----------+-----------+
|        46 |      46 |       12 |         0 |
+-----------+---------+----------+-----------+
1 row in set

要真正实现字符串连接，需要使用函数concat，参见字符串连接。

4.4 异或运算符XOR

异或运算符是用于比较两边值是否相同的。相同则返回0，不同则返回1，如果存在null，则直接返回null。

mysql> select 1 xor 1,0 xor 0,1 xor 0,1 xor null,null xor null;
+---------+---------+---------+------------+---------------+
| 1 xor 1 | 0 xor 0 | 1 xor 0 | 1 xor null | null xor null |
+---------+---------+---------+------------+---------------+
|       0 |       0 |       1 | NULL       | NULL          |
+---------+---------+---------+------------+---------------+
1 row in set

XOR运算符可以连接多次，顺序是从前向后依次进行运算。

mysql> select 1 xor 1 xor 0,1 xor 1 xor 1;
+---------------+---------------+
| 1 xor 1 xor 0 | 1 xor 1 xor 1 |
+---------------+---------------+
|             0 |             1 |
+---------------+---------------+
1 row in set

4.5 MySQL中的转义

在MySQL中某些符号需要使用反斜杠"\"来转义。包括单引号(')、双引号(")、反斜线(\)。

另外，如果对象名使用了关键字或特殊符号，也需要进行转义，如列名使用了int关键字，表名使用了char关键字等。但是此时的转义符号不是反斜线，而是反引号``或引号。

而在SQL Server中则不需要转义，有歧义的时候只需使用引号即可。

mysql> INSERT INTO test7 VALUES('\\'),('\\\\'),('\'');
mysql> select * from test7;
+-----+
| col |
+-----+
| \   |
| \\  |
| '   |
+-----+
3 rows in set

对于对象名的转义，参考show create table table_name的结果即可看到，处处使用了反引号防止歧义。例如：

mysql> show create table test7;
+-------+----------------------------------------------------------------------------+
| Table | Create Table                                                               |
+-------+----------------------------------------------------------------------------+
| test7 | CREATE TABLE `test7` (
  `col` char(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------+
1 row in set

有时候，直接反斜线转义单引号、双引号是不可行的，这时可以将双引号放在单引号中保留双引号，或者单引号放在双引号中保留单引号。

insert into t values('Tun"er'); # 保留双引号
insert into t values("Tun'er"); # 保留单引号

如果情况还更复杂，则需要采用另一种机制保留单引号、双引号：写两次表示转义一个引号。例如：

insert into t values("Tun"'"er"); # 错误
insert into t values('Tun''er'); # 正确-->Tun'er
insert into t values("Tun""er"); # 正确-->Tun"er
posted @ 2017-09-25 16:25  骏马金龙  阅读(15674)  评论(1)  编辑  收藏  举报