我们在使用mysqldump将数据库内容导出之后，重新导入到新的库中，出现了报错。

问题描述及解决办法

插入的表中其中有一个字段信息：`end_time` datetime not null default ‘0000-00-00 00:00:00’ comment ‘end time’，明显想要插入年月日未0的值。

mysql中查询：select @@sql_mode或者select @@global.sql_mode查询出数据库严格模式的相关信息。mysql在5.7之后默认使用严格模式。

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@global.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看出有这两个属性：NO_ZERO_IN_DATE,NO_ZERO_DATE

NO_ZERO_DATE影响月日为0的插入，NO_ZERO_IN_DATE影响年月日为0的插入，会影响到DATE、DATETIME、TIMESTAMP三种字段类型中对日期部分为0的处理。

####设置当前当前会话去掉NO_ZERO_IN_DATE,NO_ZERO_DATE

mysql>set session sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

再次插入表成功。

source 命令导入

source 命令导入数据库需要先登录到数库终端：

mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

很多时候我们在做数据插入的时候需要判断一下当前我插入的数据是否已经存在于数据库中。

一般情况下我们会写两条sql去操作

select 查询当前插入的数据是否已经存在如果不存在就插入新的记录，如果存在就提示用户该记录已经存在在mysql里面可以这么操作，如果记录存在就跳过或者更新，不存在就插入！

IGNORE

这个是根据插入表的主键和唯一索引去匹配的，如果当前插入的记录存在则跳过不插入。

示例：

INSERT IGNORE INTO `user` (‘phone’) VALUES (‘18888888888’);

此时user表中phone字段是唯一的，如果有这条记录则不执行插入操作，如果出现重复数据，将不返回错误，只是以警告形式返回。

on duplicate key update

主键或者唯一索引有重复时，则执行update操作

示例：

假设有一张表，id为主键，并且表中已经存在了id=1,likes=5的记录，此时下面两条sql等价。

INSERT INTO TABLENAME (id,likes) VALUES (1,18) ON DUPLICATE KEY UPDATE likes=likes+1;

UPDATE TABLENAME SET likes=likes+1 WHERE id=1;

如果是插入值，返回受影响行数为1，如果执行了更新操作那么返回的受影响行数为2.

如果条件是针对于主键操作的，那么此操作是无效操作，等同于ignore

如果一次性插入多条记录，此时id为主键，或者a是一个unique索引，并且表中已经存在的记录。

INSERT INTO TABLENAME (id,likes) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE likes = likes+1;

执行后likes的值为变成5，因为在插入前已经存在了id=1的记录，上面的sql等于执行了两条update语句。

NOTE：INSERT INTO TABLENAME……ON DUPLICATE KEY UPDATE……，这样的sql中，假如插入的记录在表中一种有重复值（与主键、唯一索引匹配），那么则会执行update操作，如果在插入的时候表中没有重复值，那么就会执行insert操作。

问题：现在问题来了，如果INSERT多行记录， ON DUPLICATE KEY UPDATE后面字段的值怎么指定？要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE，到底他会更新一行记录，还是更新所有需要更新的行。这个问题困扰了我很久了，其实使用VALUES()函数一切问题都解决了。

如果表中已经有id=1，likes=18 ；id=2，likes=19的两条记录，如果插入语句中有id=1或者id=2的记录，则更新原有记录，否则插入新行。

最后强调一下：

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！

INSERT……SELECT……WHERE NOT EXISTS

NOTE：根据查询条件判断是否已经有对应的值，有的话跳过，没有的话执行插入，随便想查什么就查什么，这个用得最多。

REPLACE INTO

如果存与主键或者唯一索引相同的记录，那么则会先删除，然后在插入新记录。

注意：如果有两个主键或者唯一索引，必须要值全部匹配才会执行替换操作，否则执行插入操作，详情见官网。

MySQL对REPLACE（和 LOAD DATA … REPLACE）使用以下算法 ：

尝试将新行插入表中插入失败，因为主键或唯一索引发生重复键错误：从表中删除具有重复键值的冲突行再次尝试将新行插入表中

问题描述

今天导入SQL文件，但是执行的时候发现如下错误提示：

ERROR: 
ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''.
Bye

解决办法

登入的时候加–binary-mode=1

mysql --binary-mode=1 -h127.0.0.1 -uroot -p123456 -P3306
mysql> use db;
mysql> source /data/jiloc.sql

最近项目中在写sql时，遇到要按字符串类型的数字排序，直接排序不行，那就需要把字符串类型转换成数字后，再进行排序。

字符串格式的话排序是这样的：

1 10 11 12 2 20 21 …

这种排列顺序不是我们想要的。

想要转化成数字，变成 1 2 10 11 20 21 的顺序。

三种解决方法：

1.数字后面直接加0，即：(value+0)格式

select * from sys_user order by (sort+0) desc

2.使用cast()函数，即：cast(value as type）格式

select * from sys_user order by cast(sort as signed) desc

3.使用convert()函数，即：convert(value , type)格式

select * from sys_user order by convert(sort , signed) desc

备注：value 代表字符串类型的字段 ，type代表要转换的类型 signed为整数类型

共享锁，又称为读锁，获得共享锁之后，可以查看但无法修改和删除数据。

排他锁，又称为写锁、独占锁，获得排他锁之后，既能读数据，又能修改数据。

为什么要加锁

很多人都知道，锁是用来解决并发问题的，那么什么是并发问题呢？并发情况下，不加锁会有什么问题呢？

拿生活中的洗手间举例子，每个洗手间都会有一个门，并且是可以上锁的，当我们进入洗手间之后会把门反锁，当我们出来之后再把锁打开。

当门被锁上之后，其他人只能在门外等待。洗手间之所以要有门锁，就是为了保护隐私的，避免出现多个人同时进入洗手间的情况。

这和数据库中的锁其实是一样的，为了避免多个事务同时操作数据库导致数据异常，一般会通过锁机制解决。

在介绍共享锁和排他锁之前，我们先来大个比方，前面已经用了一个洗手间的例子，那么就继续这个例子。一般情况下，我们进入洗手间有可能做一下几件事：洗手、化妆、上厕所等，其实只要上厕所这件事是极度隐私的，而其他事没有那么隐私。

我们可以认为洗手间就是一个数据库表，而洗手间内部的设施就是数据库表中的数据。我们每个想要进入洗手间的人都是一个事务，简单的洗手、化妆等操作可以认为是读操作，而上厕所操作可以认为是写操作。

共享锁

前面简单介绍了数据库与洗手间之间的类比关系，那么接下来继续分析什么是共享锁。

有些时候，如果我们进入洗手间只是想洗手的话，我们一般不会锁门。而其他人也可以进来洗手、化妆等。但是，其他人是不可以进来上厕所的。

这就是共享锁，也叫读锁。就是我们对数据进行读取操作的时候，其实是不会改变数据的值的。

所以我们可以给数据库增加读锁，获得读锁的事务就可以读取数据了。当数据库已经被别人增加了读锁的时候，其他新来的事务也可以读数据，但是不能写。

也就是说，如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁。

SELECT ... LOCK IN SHARE MODE;

当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁

介绍完了共享锁后，在来说说互斥锁。

如果我们进入洗手间只是想洗手，那么我们可以允许其他人也进来洗手。但是，如果我们进入洗手间是为了上厕所，那么任何人不能再进来做任何事。

这就是排他锁，也叫写锁。就是我们对数据进行写操作的时候，要先获得写锁，获得写锁的事务既可以写数据也可以读数据。但是，如果数据库已经被别人增加了排他写锁，那么后面的事务是无法在获得该数据库的任何锁的。

也就是说，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁

SELECT ... FOR UPDATE;

当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

加锁原则

拿MySql的InnoDB引擎来说，对于insert、update、delete等操作。会自动给涉及的数据加排他锁；

对于一般的select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。

共享锁：SELECT ... LOCK IN SHARE MODE;

排他锁：SELECT ... FOR UPDATE;