MYSQL中 INSERT…. ON DUPLICATE KEY UPDATE重复插入时更新及REPLACE讲解

 mysql  MYSQL中 INSERT…. ON DUPLICATE KEY UPDATE重复插入时更新及REPLACE讲解已关闭评论
9月 052019
 

个人总结:

INSERT…. ON DUPLICATE KEY UPDATE 相当于 INSERT + UPDATE的结合体

REPLACE 相当于 DELETE + INSERT的结合体

 

mysql当插入重复时更新的方法:

第一种方法:

 

示例一:插入多条记录

假设有一个主键为 client_id 的 clients 表,可以使用下面的语句:

 

Sql代码

INSERT INTO clients

(client_id,client_name,client_type)

SELECT supplier_id,supplier_name,‘advertising’

FROM suppliers

WHERE not exists(select * from clients where clients.client_id=suppliers.supplier_id);

 

示例一:插入单条记录

 

Sql代码

INSERT INTO clients

(client_id,client_name,client_type)

SELECT 10345,‘IBM’,‘advertising’

FROM dual

WHERE not exists (select * from clients where clients.client_id=10345);

 

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中。

第二种方法:

 

INSERT 中ON DUPLICATE KEY UPDATE的使用(本文重点)

如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

 

Sql代码

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

mysql>UPDATE table SET c=c+1 WHERE a=1;

 

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。

注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:

 

Sql代码

mysql>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

 

如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。

示例:

 

Sql代码

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

 

本语句与以下两个语句作用相同:

 

Sql代码

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE c=3;

mysql>INSERT INTO table (a,b,c) VALUES (4,5,6)

ON DUPLICATE KEY UPDATE c=9;

 

当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

第三种方法:

 

REPLACE语句

我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用DELETE语句删除原先的记录,然后再使用INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案,这就是REPLACE语句。使用REPLACE插入一条记录时,如果不重复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。

使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一,形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。

在使用REPLACE时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则REPLACE就和INSERT完全一样的。

在执行REPLACE后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了DELETE删除这条记录,然后再记录用INSERT来插入这条记录。如果返回的值大于2,那说明有多个唯一索引,有多条记录被删除和插入。

REPLACE的语法和INSERT非常的相似,如下面的REPLACE语句是插入或更新一条记录。

REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山’, 50);

 

 

插入多条记录:

REPLACE INTO users(id, name, age)

Sql代码

VALUES(123, ‘赵本山’, 50), (134,‘Mary’,15);

REPLACE也可以使用SET语句

REPLACE INTO users SET id = 123, name = ‘赵本山’, age = 50;

 

上面曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。

CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

 

假设table1中已经有了3条记录

a b c

1 1 1

2 2 2

3 3 3

下面我们使用REPLACE语句向table1中插入一条记录。

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

 

返回的结果如下

Query OK, 4 rows affected (0.00 sec)

在table1中的记录如下

a b c

1 2 3

 

转自: https://www.iteye.com/blog/lobert-1604122

CentOS 7 下安装 MySQL 5.7

 centos, mysql  CentOS 7 下安装 MySQL 5.7已关闭评论
8月 212019
 

原文链接:https://blog.csdn.net/u011886447/article/details/79796802

从 CentOS 7 系统开始,MariaDB 成为 yum 源中默认的数据库安装包。在 CentOS 7 及以上的系统中使用 yum 安装 MySQL 包将无法使用 MySQL。您可以选择使用完全兼容的 MariaDB,或依照本文介绍配置来继续使用 MySQL。本文以在 CentOS 7 下安装 MySQL 5.7.21 为例。

 

  1. 检查 MariaDB 是否安装

yum list installed | grep mariadb

 

 

 

  1. 卸载全部MariaDB 相关

yum -y remove mariadb*

 

  1. 下载 MySQL 的 YUM 源

进入到要下载到的路径:cd /usr/local/src

 

下载:wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

 

  1. 安装MySQL 的 YUM 源

rpm -ivh mysql57-community-release-el7-11.noarch.rpm

 

  1. 检查MySQL 的 YUM 源是否安装成功

yum repolist enabled | grep “mysql.*-community.*”

 

 

 

如图所示则安装成功。

 

  1. 查看 MySQL 版本

yum repolist all | grep mysql

 

 

 

  1. 安装 MySQL

yum install mysql-community-server

 

一直输 y 就可以了。

 

  1. 启动 MySQL 服务

systemctl start mysqld

 

  1. 测试连接 MySQL 服务

mysql -u root 或者 mysql

 

——————————————————————————–

 

提示:

 

刚安装的 MySQL 是没有密码的,这时如果出现:

 

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO),解决如下:

 

① 停止 MySQL 服务:systemctl stop mysqld

 

② 以不检查权限的方式启动 MySQL: mysqld –user=root –skip-grant-tables &

 

③ 再次输入 mysql -u root 或者 mysql,这次就可以进来了。

 

④ 更新密码:

 

MySQL 5.7 以下版本:UPDATE mysql.user SET Password=PASSWORD(‘123456′) where USER=’root’;

 

MySQL 5.7 版本:UPDATE mysql.user SET authentication_string=PASSWORD(‘123456′) where USER=’root’;

 

⑤ 刷新:flush privileges;

 

⑥ 退出:exit;

 

设置完之后,输入 mysql -u root -p,这时输入刚设置的密码,就可以登进数据库了。

 

——————————————————————————–

 

  1. 防火墙设置

远程访问 MySQL,需要开放 3306 端口:

 

firewall-cmd –permanent –zone=public –add-port=3306/tcp

 

firewall-cmd –permanent –zone=public –add-port=3306/udp

 

firewall-cmd –reload

 

如果是 CentOS 7,需要将 MySQL 服务加入防火墙,然后重启防火墙:

 

firewall-cmd –zone=public –permanent –add-service=mysql

 

systemctl restart firewalld

 

——————————————————————————–

 

提示:

 

在输入 firewall-cmd –permanent –zone=public –add-port=3306/tcp 时可能会报 ‘FirewallD is not running’,是说防火墙本身就没有打开,解决方法:

 

① 查看防火墙状态:systemctl status firewalld,会发现状态是 dead,即防火墙未开启。

 

② 打开防火墙:systemctl start firewalld

 

③ 再次查看防火墙状态:systemctl status firewalld,这时会发现状态变为 running,即防火墙开启成功。

 

 

 

 

 

 

这时再输入开放 3306 端口的命令就没有问题了。

 

——————————————————————————–

 

  1. 设置允许远程访问

默认情况下 MySQL 是不允许远程连接的,所以在 Java 项目或者 MySQLWorkbench 等数据库连接工具连接服务器上的 MySQL 服务的时候会报 “Host ‘x.x.x.x’ is not allowed to connect to this MySQL server”。可以通过下面的设置解决。详细可以参考之前写的一篇文章 XXX is not allowed to connect to this MySQL server。

 

① grant all privileges on *.* to root@”%” identified by ‘0’;

 

② flush privileges;

 

——————————————————————————–

 

提示:

 

在执行第一条命令的时候,可能会报:

 

‘ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.’ 需要让我们重置密码。原因是因为我刚刚的命令中设置的数据库密码是0,这个密码过于简单,不符合 MySQL 的安全要求。只要重新设置一个复杂点的密码就可以了:

 

mysql> SET PASSWORD = PASSWORD(‘xxx’);   //xxx 是重置的新的复杂的密码

 

——————————————————————————–

 

思考:

 

之前设置简单密码是没有问题的,可能原因:

 

① 可能目前环境是 CentOS 7 + MySQL 5.7.21,安全性有所提升。

 

② 也有可能是之前的数据库设置过

 

mysql> set global validate_password_policy=0;

 

mysql> set global validate_password_length=1;

 

允许设置简单密码。

 

  1. 相关命令

MySQL 相关:

 

systemctl start mysqld    #启动mysql

 

systemctl stop mysqld    #停止mysqld

 

systemctl restart mysqld    #重启mysqld

 

systemctl enable mysqld    #设置开机启动

 

systemctl status mysqld    #查看 MySQL Server 状态

 

防火墙相关:

 

systemctl status firewalld    #查看防火墙状态

 

systemctl start firewalld    #打开防火墙

 

systemctl stop firewalld    #关闭防火墙

 

systemctl restart firewalld    #重启防火墙

 

 

MYSQL 中 ON DUPLICATE KEY UPDATE / REPLACE 单语句处理重复插入时更新

 mysql  MYSQL 中 ON DUPLICATE KEY UPDATE / REPLACE 单语句处理重复插入时更新已关闭评论
7月 252019
 

来自:https://lobert.iteye.com/blog/1604122 , 讲的很清楚,记录下

 

mysql当插入重复时更新的方法:

第一种方法:

 示例一:插入多条记录

假设有一个主键为 client_id clients 表,可以使用下面的语句:

 

Sql代码  

  1. INSERT INTO clients  
  2. (client_id,client_name,client_type)  
  3. SELECT supplier_id,supplier_name,‘advertising’  
  4. FROM suppliers  
  5. WHERE not exists(select * from clients where clients.client_id=suppliers.supplier_id);  

 

示例一:插入单条记录

Sql代码  

  1. INSERT INTO clients  
  2. (client_id,client_name,client_type)  
  3. SELECT 10345,‘IBM’,‘advertising’  
  4. FROM dual  
  5. WHERE not exists (select * from clients where clients.client_id=10345);  

 

使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中。

第二种方法: 

INSERT ON DUPLICATE KEY UPDATE的使用(本文重点)

如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

 

Sql代码  

  1. mysql>INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;  
  2. mysql>UPDATE table SET c=c+1 WHERE a=1;  

 

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2

注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:

 

Sql代码  

  1. mysql>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;  

 

如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。

您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL

示例:

 

Sql代码  

  1. mysql>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  
  2.           ->ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);  

 

本语句与以下两个语句作用相同:

 

Sql代码  

  1. mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)  
  2.           ->ON DUPLICATE KEY UPDATE c=3;  
  3. mysql>INSERT INTO table (a,b,c) VALUES (4,5,6)  
  4.           ->ON DUPLICATE KEY UPDATE c=9;  

 

当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

第三种方法:

 

REPLACE语句

  我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用DELETE语句删除原先的记录,然后再使用INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案,这就是REPLACE语句。使用REPLACE插入一条记录时,如果不重复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。

  使用REPLACE的最大好处就是可以将DELETEINSERT合二为一,形成一个原子操作。这样就可以不必考虑在同时使用DELETEINSERT时添加事务等复杂操作了。

  在使用REPLACE时,表中必须有唯一索引,而且这个索引所在的字段不能允许空值,否则REPLACE就和INSERT完全一样的。

  在执行REPLACE后,系统返回了所影响的行数,如果返回1,说明在表中并没有重复的记录,如果返回2,说明有一条重复记录,系统自动先调用了DELETE删除这条记录,然后再记录用INSERT来插入这条记录。如果返回的值大于2,那说明有多个唯一索引,有多条记录被删除和插入。

  REPLACE的语法和INSERT非常的相似,如下面的REPLACE语句是插入或更新一条记录。

  REPLACE INTO users (id,name,age) VALUES(123, ‘赵本山‘, 50);

  

 

  插入多条记录:

  REPLACE INTO users(id, name, age)

Sql代码  

  1. VALUES(123, 赵本山, 50), (134,‘Mary’,15);  
  2.   
  3. REPLACE也可以使用SET语句  
  4.   
  5. REPLACE INTO users SET id = 123, name = 赵本山, age = 50;  

 

  上面曾提到REPLACE可能影响3条以上的记录,这是因为在表中有超过一个的唯一索引。在这种情况下,REPLACE将考虑每一个唯一索引,并对每一个索引对应的重复记录都删除,然后插入这条新记录。假设有一个table1表,有3个字段a, b, c。它们都有一个唯一索引。

  CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

 

  假设table1中已经有了3条记录

  a b c

  1 1 1

  2 2 2

  3 3 3

  下面我们使用REPLACE语句向table1中插入一条记录。

  REPLACE INTO table1(a, b, c) VALUES(1,2,3);

 

  返回的结果如下

  Query OK, 4 rows affected (0.00 sec)

  在table1中的记录如下

  a b c

  1 2 3

 

使用MYSQL命令查看MYSQL中数据库或表占用空间的大小

 mysql  使用MYSQL命令查看MYSQL中数据库或表占用空间的大小已关闭评论
3月 062019
 

知道每个数据库的大小,使用下面步骤:

1、进入information_schema 数据库

use information_schema;

 

2、根据表名汇总查询各表数据的大小:

select TABLE_SCHEMA as tablename, concat(round(sum(data_length/1024/1024/1024),3),’GB’) as data from tables group by TABLE_SCHEMA;

 

2、查看指定数据库的大小:

比如查看数据库mydb的大小

select concat(round(sum(data_length/1024/1024/1024),3),’GB’) as data from tables where table_schema=’mydb’;

 

4、查看指定数据库的某个表的大小

比如查看数据库mydb中 mytable 表的大小

select concat(round(sum(data_length/1024/1024),3),’MB’) as data from tables where table_schema=’mydb’ and table_name=’mytable’;

navcat 连接本地mysql,出现“client does not support authentication”

 mysql  navcat 连接本地mysql,出现“client does not support authentication”已关闭评论
12月 242018
 

navcat 使用root连接本地mysql,出现“client does not support authentication”

先使用命令行mysql -uroot -p连接mysql, 输入密码后进入

mysql>

然后运行以下命令如下:

1、mysql> use mysql;

2、mysql> alter user ‘root’@’localhost’ identified with mysql_native_password by ‘********’;

3、mysql> flush privileges;

MySQL的if、case语句使用总结

 mysql  MySQL的if、case语句使用总结已关闭评论
11月 212018
 

总结的不错,https://www.cnblogs.com/raobenjun/p/7998467.html

Mysql的if既可以作为表达式用,也可在存储过程中作为流程控制语句使用,如下是做为表达式使用:

IF表达式

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。

select *,if(sva=1,"男","女") as ssva from taname where sva != ""

作为表达式的if也可以用CASE when来实现:

select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != ''

在第一个方案的返回结果中, value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值,则返回结果为ELSE后的结果,如果没有ELSE 部分,则返回值为 NULL。

例如:

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END as testCol

将输出one

IFNULL(expr1,expr2)

假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串,具体情况取决于其所使用的语境。

mysql> SELECT IFNULL(1,0); -> 1 mysql> SELECT IFNULL(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'yes'); -> 'yes'

IFNULL(expr1,expr2) 的默认结果值为两个表达式中更加“通用”的一个,顺序为STRING、 REAL或 INTEGER。

IF ELSE 做为流程控制语句使用

if实现条件判断,满足不同条件执行不同的操作,这个我们只要学编程的都知道if的作用了,下面我们来看看mysql 存储过程中的if是如何使用的吧。

IF search_condition THEN 
    statement_list [ELSEIF search_condition THEN] statement_list ... [ELSE 
    statement_list] END IF 

与PHP中的IF语句类似,当IF中条件search_condition成立时,执行THEN后的statement_list语句,否则判断ELSEIF中的条件,成立则执行其后的statement_list语句,否则继续判断其他分支。当所有分支的条件均不成立时,执行ELSE分支。search_condition是一个条件表达式,可以由“=、<、<=、>、>=、!=”等条件运算符组成,并且可以使用AND、OR、NOT对多个表达式进行组合。

例如,建立一个存储过程,该存储过程通过学生学号(student_no)和课程编号(course_no)查询其成绩(grade),返回成绩和成绩的等级,成绩大于90分的为A级,小于90分大于等于80分的为B级,小于80分大于等于70分的为C级,依次到E级。那么,创建存储过程的代码如下:

create procedure dbname.proc_getGrade (stu_no varchar(20),cour_no varchar(10)) BEGIN declare stu_grade float; select grade into stu_grade from grade where student_no=stu_no and course_no=cour_no; if stu_grade>=90 then select stu_grade,'A'; elseif stu_grade<90 and stu_grade>=80 then select stu_grade,'B'; elseif stu_grade<80 and stu_grade>=70 then select stu_grade,'C'; elseif stu_grade70 and stu_grade>=60 then select stu_grade,'D'; else select stu_grade,'E'; end if; END

注意:IF作为一条语句,在END IF后需要加上分号“;”以表示语句结束,其他语句如CASE、LOOP等也是相同的。

mysql取出每个分组中最新的记录

 mysql  mysql取出每个分组中最新的记录已关闭评论
2月 112018
 

原来group还能这样写。

mysql的gruopby分组功能没有排序功能,所以我们如果想取出某个分组下的最新记录是不太容易的,下面介绍两种方法,一种是通过子查询,一种是通过group_concat函数来实现。

一、表结构及数据插入
#表的结构 `test3`
CREATE TABLE IF NOT EXISTS `test3` (
`id` int(11) NOT NULL auto_increment,
`bid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
`dtime` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
#转存表中的数据 `test3`
INSERT INTO `test3` (`id`, `bid`, `cid`, `dtime`) VALUES
(1, 1, 3, ’2014-03-18 16:00:00′),
(2, 1, 10, ’2014-03-18 17:00:00′),
(3, 2, 5, ’2014-03-18 18:00:00′),
(4, 2, 6, ’2014-03-18 19:00:00′),
(5, 1, 7, ’2014-03-18 20:00:00′);
二、通过子查询实现(此方法在mysql5.7.21上已不支持, 可以使用第2个方法group_concat)
1、sql语句
select * from(select * from test3 ORDER BY dtime DESC) as tempGROUP BY  bid ORDER BY dtime DESC;
三、通过group_concat函数
1、完整的语法
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator’分隔符’])
作用:将要连接的字段按照排序字段的顺序用分隔符连起来显示,默认分隔符是”,”。
2、sql语句
select substring_index(group_concat(id order by `dtime`desc),’,’,1) as id, substring_index(group_concat(bid order by`dtime` desc),’,’,1) as bid,substring_index(group_concat(cid orderby `dtime` desc),’,’,1) as cid,substring_index(group_concat(dtimeorder by `dtime` desc),’,’,1) as dtime from `test3` group bybid;
两上结果是一致的,虽然mysql自身的group by没有排序功能,但是通过自已的思考还是有办法的,写此作为备忘吧。
select * from (SELECT * FROM jcacard.v_inoroutrecord order by RecordDateTime desc) as temp 
group by EmployName
having InOrOut = 1
order by RecordDateTime

转自:http://blog.csdn.net/swazer_z/article/details/70054315

深入mysql “ON DUPLICATE KEY UPDATE” 语法的分析

 mysql  深入mysql “ON DUPLICATE KEY UPDATE” 语法的分析已关闭评论
9月 042017
 

本篇文章是对mysql “ON DUPLICATE KEY UPDATE”语法进行了详细的分析介绍,来自:http://www.jb51.net/article/39255.htm

mysql “ON DUPLICATE KEY UPDATE” 语法
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。 
例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果:

复制代码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

如果行作为新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2。 
这个语法还可以这样用: 
如果INSERT多行记录(假设 a 为主键或 a 是一个 UNIQUE索引列):

复制代码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;

执行后, c 的值会变为 4 (第二条与第一条重复, c 在原值上+1).

复制代码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);

执行后, c 的值会变为 7 (第二条与第一条重复, c 在直接取重复的值7). 
注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法! 
这个语法和适合用在需要 判断记录是否存在,不存在则插入存在则更新的场景.

INSERT INTO .. ON DUPLICATE KEY更新多行记录
如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE;如果不会导致唯一值列重复的问题,则插入新行。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

复制代码代码如下:

INSERT INTO TABLE (a,b,c) 
VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
如果你想了解更多关于INSERT INTO .. ON DUPLICATE KEY的功能说明,详见MySQL参考文档:13.2.4. INSERT语法

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

举个例子,字段a被定义为UNIQUE,并且原数据库表table中已存在记录(2,2,9)和(3,2,1),如果插入记录的a值与原有记录重复,则更新原有记录,否则插入新行:

复制代码代码如下:

INSERT INTO TABLE (a,b,c) VALUES 
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

以上SQL语句的执行,发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突,则执行ON DUPLICATE KEY UPDATE,将原有记录(2,2,9)更新成(2,5,9),将(3,2,1)更新成(3,3,1),插入新记录(1,2,3)和(4,8,2)
注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!

MySQL 中随机抽样 ORDER BY RAND() 调优

 mysql  MySQL 中随机抽样 ORDER BY RAND() 调优已关闭评论
4月 222016
 

正好使用到ORDER BY RAND() ,网上看到网友的优化方案,收集下。

来自:http://zhidao.baidu.com/link?url=Gfc6t89OKVpxKRIGYnJ7ug1OpTC3bm8GgfkP_a_hrQmKmnOVfnPiOLhH4BwilRwz5Y5VfxoiheJWVVM3G4523K 

要从tablename表中随机提取一条记录,大家一般的写法就是:SELECT * FROM tablename ORDER BY RAND() LIMIT 1。
但是,后来我查了一下MYSQL的官方手册,里面针对RAND()的提示大概意思就是,在ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描。但是在MYSQL 3.23版本中,仍然可以通过ORDER BY RAND()来实现随机。

但是真正测试一下才发现这样效率非常低。一个15万余条的库,查询5条数据,居然要8秒以上。查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.
搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据。
SELECT * 
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 
WHERE t1.id >= t2.id 
ORDER BY t1.id ASC LIMIT 5;

但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。

下面的语句采用的是JOIN,mysql的论坛上有人使用
SELECT * 
FROM `table` 
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) 
ORDER BY id LIMIT 1;

我测试了一下,需要0.5秒,速度也不错,但是跟上面的语句还是有很大差距。总觉有什么地方不正常。

于是我把语句改写了一下。
SELECT * FROM `table` 
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) 
ORDER BY id LIMIT 1;

这下,效率又提高了,查询时间只有0.01秒

最后,再把语句完善一下,加上MIN(id)的判断。我在最开始测试的时候,就是因为没有加上MIN(id)的判断,结果有一半的时间总是查询到表中的前面几行。
完整查询语句是:
SELECT * FROM `table` 
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) 
ORDER BY id LIMIT 1;

SELECT * 
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2 
WHERE t1.id >= t2.id 
ORDER BY t1.id LIMIT 1;

最后在php中对这两个语句进行分别查询10次,
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。

导出mysql中某个数据库/所有数据库中所有表/某张表的记录的 某(前) 几条记录(使用mysqldump)

 mysql  导出mysql中某个数据库/所有数据库中所有表/某张表的记录的 某(前) 几条记录(使用mysqldump)已关闭评论
3月 112016
 

如果需要导出mysql中某个数据库/所有数据库中所有表/某张表的记录的 某(前) 几条记录,应该怎么办?

可以使用下面的类似命令:

/usr/bin/mysqldump -S /var/run/mysqld/mysqld.sock -uroot -p 单个数据库名称(或者–all-databases=True表示所有数据库) –where “1=1 limit 3” –complete-insert=True –lock-all-tables=True > db_limit_3.sql

上面语句表示导出 某个数据库中所有表的前三条记录!


具体说明下mysqldump中参数(以mysql5.5.19为例, 如不同版本大家可以通过mysqldump –help查看具体的参数):

参数

参数说明

–all-databases  , -A

导出全部数据库。

mysqldump  -uroot -p –all-databases

–all-tablespaces  , -Y

导出全部表空间。

mysqldump  -uroot -p –all-databases –all-tablespaces

–no-tablespaces  , -y

不导出任何表空间信息。

mysqldump  -uroot -p –all-databases –no-tablespaces

–add-drop-database

每个数据库创建之前添加drop数据库语句。

mysqldump  -uroot -p –all-databases –add-drop-database

–add-drop-table

每个数据表创建之前添加drop数据表语句。(默认为打开状态,使用–skip-add-drop-table取消选项)

mysqldump  -uroot -p –all-databases  (默认添加drop语句)

mysqldump  -uroot -p –all-databases –skip-add-drop-table  (取消drop语句)

–add-locks

在每个表导出之前增加LOCK TABLES并且之后UNLOCK  TABLE。(默认为打开状态,使用–skip-add-locks取消选项)

mysqldump  -uroot -p –all-databases  (默认添加LOCK语句)

mysqldump  -uroot -p –all-databases –skip-add-locks   (取消LOCK语句)

–allow-keywords

允许创建是关键词的列名字。这由表名前缀于每个列名做到。

mysqldump  -uroot -p –all-databases –allow-keywords

–apply-slave-statements

在’CHANGE MASTER’前添加’STOP SLAVE’,并且在导出的最后添加’START SLAVE’。

mysqldump  -uroot -p –all-databases –apply-slave-statements

–character-sets-dir

字符集文件的目录

mysqldump  -uroot -p –all-databases  –character-sets-dir=/usr/local/mysql/share/mysql/charsets

–comments

附加注释信息。默认为打开,可以用–skip-comments取消

mysqldump  -uroot -p –all-databases  (默认记录注释)

mysqldump  -uroot -p –all-databases –skip-comments   (取消注释)

–compatible

导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等,

要使用几个值,用逗号将它们隔开。它并不保证能完全兼容,而是尽量兼容。

mysqldump  -uroot -p –all-databases –compatible=ansi

–compact

导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项:–skip-add-drop-table  –skip-add-locks –skip-comments –skip-disable-keys

mysqldump  -uroot -p –all-databases –compact

–complete-insert,  -c

使用完整的insert语句(包含列名称)。这么做能提高插入效率,但是可能会受到max_allowed_packet参数的影响而导致插入失败。

mysqldump  -uroot -p –all-databases –complete-insert

–compress, -C

在客户端和服务器之间启用压缩传递所有信息

mysqldump  -uroot -p –all-databases –compress

–create-options,  -a

在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)

mysqldump  -uroot -p –all-databases

–databases,  -B

导出几个数据库。参数后面所有名字参量都被看作数据库名。

mysqldump  -uroot -p –databases test mysql

–debug

输出debug信息,用于调试。默认值为:d:t:o,/tmp/mysqldump.trace

mysqldump  -uroot -p –all-databases –debug

mysqldump  -uroot -p –all-databases –debug=” d:t:o,/tmp/debug.trace”

–debug-check

检查内存和打开文件使用说明并退出。

mysqldump  -uroot -p –all-databases –debug-check

–debug-info

输出调试信息并退出

mysqldump  -uroot -p –all-databases –debug-info

–default-character-set

设置默认字符集,默认值为utf8

mysqldump  -uroot -p –all-databases –default-character-set=latin1

–delayed-insert

采用延时插入方式(INSERT DELAYED)导出数据

mysqldump  -uroot -p –all-databases –delayed-insert

–delete-master-logs

master备份后删除日志. 这个参数将自动激活–master-data。

mysqldump  -uroot -p –all-databases –delete-master-logs

–disable-keys

对于每个表,用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件,因为它是在插入所有行后创建索引的。该选项只适合MyISAM表,默认为打开状态。

mysqldump  -uroot -p –all-databases 

–dump-slave

该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时,将会以CHANGE MASTER命令输出到数据文件;设置为2时,在命令前增加说明信息。该选项将会打开–lock-all-tables,除非–single-transaction被指定。该选项会自动关闭–lock-tables选项。默认值为0。

mysqldump  -uroot -p –all-databases –dump-slave=1

mysqldump  -uroot -p –all-databases –dump-slave=2

–events, -E

导出事件。

mysqldump  -uroot -p –all-databases –events

–extended-insert,  -e

使用具有多个VALUES列的INSERT语法。这样使导出文件更小,并加速导入时的速度。默认为打开状态,使用–skip-extended-insert取消选项。

mysqldump  -uroot -p –all-databases

mysqldump  -uroot -p –all-databases–skip-extended-insert   (取消选项)

–fields-terminated-by

导出文件中忽略给定字段。与–tab选项一起使用,不能用于–databases和–all-databases选项

mysqldump  -uroot -p test test –tab=”/home/mysql” –fields-terminated-by=”#”

–fields-enclosed-by

输出文件中的各个字段用给定字符包裹。与–tab选项一起使用,不能用于–databases和–all-databases选项

mysqldump  -uroot -p test test –tab=”/home/mysql” –fields-enclosed-by=”#”

–fields-optionally-enclosed-by

输出文件中的各个字段用给定字符选择性包裹。与–tab选项一起使用,不能用于–databases和–all-databases选项

mysqldump  -uroot -p test test –tab=”/home/mysql”  –fields-enclosed-by=”#” –fields-optionally-enclosed-by  =”#”

–fields-escaped-by

输出文件中的各个字段忽略给定字符。与–tab选项一起使用,不能用于–databases和–all-databases选项

mysqldump  -uroot -p mysql user –tab=”/home/mysql” –fields-escaped-by=”#”

–flush-logs

开始导出之前刷新日志。

请注意:假如一次导出多个数据库(使用选项–databases或者–all-databases),将会逐个数据库刷新日志。除使用–lock-all-tables或者–master-data外。在这种情况下,日志将会被刷新一次,相应的所以表同时被锁定。因此,如果打算同时导出和刷新日志应该使用–lock-all-tables 或者–master-data 和–flush-logs。

mysqldump  -uroot -p –all-databases –flush-logs

–flush-privileges

在导出mysql数据库之后,发出一条FLUSH  PRIVILEGES 语句。为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。

mysqldump  -uroot -p –all-databases –flush-privileges

–force

在导出过程中忽略出现的SQL错误。

mysqldump  -uroot -p –all-databases –force

–help

显示帮助信息并退出。

mysqldump  –help

–hex-blob

使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。

mysqldump  -uroot -p –all-databases –hex-blob

–host, -h

需要导出的主机信息

mysqldump  -uroot -p –host=localhost –all-databases

–ignore-table

不导出指定表。指定忽略多个表时,需要重复多次,每次一个表。每个表必须同时指定数据库和表名。例如:–ignore-table=database.table1 –ignore-table=database.table2 ……

mysqldump  -uroot -p –host=localhost –all-databases –ignore-table=mysql.user

–include-master-host-port

在–dump-slave产生的’CHANGE  MASTER TO..’语句中增加’MASTER_HOST=<host>,MASTER_PORT=<port>’  

mysqldump  -uroot -p –host=localhost –all-databases –include-master-host-port

–insert-ignore

在插入行时使用INSERT IGNORE语句.

mysqldump  -uroot -p –host=localhost –all-databases –insert-ignore

–lines-terminated-by

输出文件的每行用给定字符串划分。与–tab选项一起使用,不能用于–databases和–all-databases选项。

mysqldump  -uroot -p –host=localhost test test –tab=”/tmp/mysql”  –lines-terminated-by=”##”

–lock-all-tables,  -x

提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭–single-transaction 和–lock-tables 选项。

mysqldump  -uroot -p –host=localhost –all-databases –lock-all-tables

–lock-tables,  -l

开始导出前,锁定所有表。用READ  LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB,–single-transaction是一个更好的选择,因为它根本不需要锁定表。

请注意当导出多个数据库时,–lock-tables分别为每个数据库锁定表。因此,该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

mysqldump  -uroot -p –host=localhost –all-databases –lock-tables

–log-error

附加警告和错误信息到给定文件

mysqldump  -uroot -p –host=localhost –all-databases  –log-error=/tmp/mysqldump_error_log.err

–master-data

该选项将binlog的位置和文件名追加到输出文件中。如果为1,将会输出CHANGE MASTER 命令;如果为2,输出的CHANGE  MASTER命令前添加注释信息。该选项将打开–lock-all-tables 选项,除非–single-transaction也被指定(在这种情况下,全局读锁在开始导出时获得很短的时间;其他内容参考下面的–single-transaction选项)。该选项自动关闭–lock-tables选项。

mysqldump  -uroot -p –host=localhost –all-databases –master-data=1;

mysqldump  -uroot -p –host=localhost –all-databases –master-data=2;

–max_allowed_packet

服务器发送和接受的最大包长度。

mysqldump  -uroot -p –host=localhost –all-databases –max_allowed_packet=10240

–net_buffer_length

TCP/IP和socket连接的缓存大小。

mysqldump  -uroot -p –host=localhost –all-databases –net_buffer_length=1024

–no-autocommit

使用autocommit/commit 语句包裹表。

mysqldump  -uroot -p –host=localhost –all-databases –no-autocommit

–no-create-db,  -n

只导出数据,而不添加CREATE DATABASE 语句。

mysqldump  -uroot -p –host=localhost –all-databases –no-create-db

–no-create-info,  -t

只导出数据,而不添加CREATE TABLE 语句。

mysqldump  -uroot -p –host=localhost –all-databases –no-create-info

–no-data, -d

不导出任何数据,只导出数据库表结构。

mysqldump  -uroot -p –host=localhost –all-databases –no-data

–no-set-names,  -N

等同于–skip-set-charset

mysqldump  -uroot -p –host=localhost –all-databases –no-set-names

–opt

等同于–add-drop-table,  –add-locks, –create-options, –quick, –extended-insert, –lock-tables,  –set-charset, –disable-keys 该选项默认开启,  可以用–skip-opt禁用.

mysqldump  -uroot -p –host=localhost –all-databases –opt

–order-by-primary

如果存在主键,或者第一个唯一键,对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效,但会使得导出工作花费很长时间。 

mysqldump  -uroot -p –host=localhost –all-databases –order-by-primary

–password, -p

连接数据库密码

–pipe(windows系统可用)

使用命名管道连接mysql

mysqldump  -uroot -p –host=localhost –all-databases –pipe

–port, -P

连接数据库端口号

–protocol

使用的连接协议,包括:tcp, socket, pipe, memory.

mysqldump  -uroot -p –host=localhost –all-databases –protocol=tcp

–quick, -q

不缓冲查询,直接导出到标准输出。默认为打开状态,使用–skip-quick取消该选项。

mysqldump  -uroot -p –host=localhost –all-databases 

mysqldump  -uroot -p –host=localhost –all-databases –skip-quick

–quote-names,-Q

使用(`)引起表和列名。默认为打开状态,使用–skip-quote-names取消该选项。

mysqldump  -uroot -p –host=localhost –all-databases

mysqldump  -uroot -p –host=localhost –all-databases –skip-quote-names

–replace

使用REPLACE INTO 取代INSERT INTO.

mysqldump  -uroot -p –host=localhost –all-databases –replace

–result-file,  -r

直接输出到指定文件中。该选项应该用在使用回车换行对(\\r\\n)换行的系统上(例如:DOS,Windows)。该选项确保只有一行被使用。

mysqldump  -uroot -p –host=localhost –all-databases –result-file=/tmp/mysqldump_result_file.txt

–routines, -R

导出存储过程以及自定义函数。

mysqldump  -uroot -p –host=localhost –all-databases –routines

–set-charset

添加’SET NAMES  default_character_set’到输出文件。默认为打开状态,使用–skip-set-charset关闭选项。

mysqldump  -uroot -p –host=localhost –all-databases 

mysqldump  -uroot -p –host=localhost –all-databases –skip-set-charset

–single-transaction

该选项在导出数据之前提交一个BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎,仅InnoDB。本选项和–lock-tables 选项是互斥的,因为LOCK  TABLES 会使任何挂起的事务隐含提交。要想导出大表的话,应结合使用–quick 选项。

mysqldump  -uroot -p –host=localhost –all-databases –single-transaction

–dump-date

将导出时间添加到输出文件中。默认为打开状态,使用–skip-dump-date关闭选项。

mysqldump  -uroot -p –host=localhost –all-databases

mysqldump  -uroot -p –host=localhost –all-databases –skip-dump-date

–skip-opt

禁用–opt选项.

mysqldump  -uroot -p –host=localhost –all-databases –skip-opt

–socket,-S

指定连接mysql的socket文件位置,默认路径/tmp/mysql.sock

mysqldump  -uroot -p –host=localhost –all-databases –socket=/tmp/mysqld.sock

–tab,-T

为每个表在给定路径创建tab分割的文本文件。注意:仅仅用于mysqldump和mysqld服务器运行在相同机器上。

mysqldump  -uroot -p –host=localhost test test –tab=”/home/mysql”

–tables

覆盖–databases (-B)参数,指定需要导出的表名。

mysqldump  -uroot -p –host=localhost –databases test –tables test

–triggers

导出触发器。该选项默认启用,用–skip-triggers禁用它。

mysqldump  -uroot -p –host=localhost –all-databases –triggers

–tz-utc

在导出顶部设置时区TIME_ZONE=’+00:00′ ,以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。

mysqldump  -uroot -p –host=localhost –all-databases –tz-utc

–user, -u

指定连接的用户名。

–verbose, –v

输出多种平台信息。

–version, -V

输出mysqldump版本信息并退出

–where, -w

只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符,一定要将条件引用起来。

mysqldump  -uroot -p –host=localhost –all-databases –where=” user=’root’”

–xml, -X

导出XML格式.

mysqldump  -uroot -p –host=localhost –all-databases –xml

–plugin_dir

客户端插件的目录,用于兼容不同的插件版本。

mysqldump  -uroot -p –host=localhost –all-databases –plugin_dir=”/usr/local/lib/plugin”

–default_auth

客户端插件默认使用权限。

mysqldump  -uroot -p –host=localhost –all-databases –default-auth=”/usr/local/lib/plugin/<PLUGIN>”