sql server 分页sql高效写法(mysql limit替代写法)

 mysql, sqlserver  sql server 分页sql高效写法(mysql limit替代写法)已关闭评论
3月 182021
 

习惯了mysql， 切到sql server真TM不习惯，处处觉得sql server的使用太垃圾了， 完全是拉低开发人员效率的产品！

今天谈下sql server为人诟病的分页，OMG ， 在mysql里使用 limit n,m就能解决的事情，在sql server里动作就大了，常见的使用 NOT in 语法（实在无法想象这样的查询效率），或者使用自己定义一个存储过程的方式（但这种方式条件又不够灵活）， 推荐一个相对比较高效并且相对简单的写法如下：

SELECT TOP 页大小 * FROM( SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1 )as A 
WHERE RowNumber > 页大小*(页数-1)

注解：

  • 首先利用Row_number()为table1表的每一行添加一个行号，给行号这一列取名’RowNumber’ ，在over()方法中将table1按’id’做了升序排列
  • 然后将’RowNumber’列 与table1表的所有列 形成一个表A
  • 重点在where条件。假如当前页(currentPage)是第2页，每页显示10个数据(pageSzie)。那么第一页的数据就是第11-20条

mysql的on duplicate key update语句在sql server中的替代写法

 sqlserver, 数据库  mysql的on duplicate key update语句在sql server中的替代写法已关闭评论
3月 162021
 

mysql在处理唯一主键记录时可以一个语句实现没有记录时插入有记录时更新， 只要使用“insert …… on duplicate key update …. ”，非常简洁高效，oracle 、 DB2 都有类似的语法可以实现相同的功能，但坑爹的是sql server没有对应的语句，但可以使用下面的语句实现相同的功能，但稍许复杂些：

  • 1. 对于sql server 2008及以后的版本，可以使用merge语法， 可以参考下面的实例用法：
CREATE TABLE #mytable(COL_A VARCHAR(10), COL_B VARCHAR(10), COL_C VARCHAR(10), COL_D VARCHAR(10))
INSERT INTO #mytable VALUES('1','0.1', '0.2', '0.3'); --<These are the values we'll be updating
SELECT * FROM #mytable --< Starting values (1 row)
--< 以上是测试数据，主要注意下面的MERGE语句用法
    MERGE #mytable AS target --< This is the target we want to merge into
    USING ( --< This is the source of your merge. Can me any select statement
        SELECT '1' AS VAL_A,'1.1' AS VAL_B, '1.2' AS VAL_C, '1.3' AS VAL_D --<These are the values we'll use for the update. (Assuming column COL_A = '1' = Primary Key)
        UNION
        SELECT '2' AS VAL_A,'2.1' AS VAL_B, '2.2' AS VAL_C, '2.3' AS VAL_D) --<These values will be inserted (cause no COL_A = '2' exists)
        AS source (VAL_A, VAL_B, VAL_C, VAL_D) --< Column Names of our virtual "Source" table
    ON (target.COL_A = source.VAL_A) --< This is what we'll use to find a match "JOIN source on Target" using the Primary Key
    WHEN MATCHED THEN --< This is what we'll do WHEN we find a match, in your example, UPDATE COL_D = VALUES(COL_D);
        UPDATE SET
            target.COL_B = source.VAL_B,
            target.COL_C = source.VAL_C,
            target.COL_D = source.VAL_D
    WHEN NOT MATCHED THEN --< This is what we'll do when we didn't find a match
    INSERT (COL_A, COL_B, COL_C, COL_D)
    VALUES (source.VAL_A, source.VAL_B, source.VAL_C, source.VAL_D)
    --OUTPUT deleted.*, $action, inserted.* --< Uncomment this if you want a summary of what was inserted on updated.
    --INTO #Output  --< Uncomment this if you want the results to be stored in another table. NOTE* The table must exists
    ;

--< MERGE后测试数据显示
SELECT * FROM #mytable --< Ending values (2 row, 1 new, 1 updated)
  • 2. 对于sql server 2008以前的版本可以参考下面语句的使用方法：
update mytable
  set col_d = 'val_d'
  where col_a = 'val_a'
    and col_b = 'val_b'
    and col_c = 'val_c';

insert into mytable (col_a, col_b, col_c, col_d)
  select 'val_a','val_b', 'val_c', 'val_d'
  where not exists (select * 
    from mytable with (serializable) 
    where col_a = 'val_a'
      and col_b = 'val_b'
      and col_c = 'val_c'
      );

参考：https://stackoverflow.com/questions/27076348/equivalent-of-mysql-on-duplicate-key-update-in-sql-server

centos 7 安装 mysql 5.7 及问题总结

 mysql  centos 7 安装 mysql 5.7 及问题总结已关闭评论
12月 242020
 

centos 7中默认没有mysql 的rpm安装包，需要自己添加

在 https://dev.mysql.com/downloads/repo/yum/ 找到 yum 源 rpm 最新的安装包。

我们只下载mysql 5.7的

# 下载
shell> wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
# 安装 mysql 源
shell> yum localinstall mysql57-community-release-el7-11.noarch.rpm  # 或者 使用 rpm -ivh mysql57-community-release-el7-11.noarch.rpm

#安装mysql

shell> yum install -y mysql-community-server

#启动mysql

shell> systemctl start mysqld

#查看状态

shell> systemctl status mysqld

#设置开机启动
shell> systemctl enable mysqld
# 重载所有修改过的配置文件
shell> systemctl daemon-reload

#修改root本地账户密码
#mysql安装完成之后，生成的默认密码在 /var/log/mysqld.log 文件中。使用 grep 命令找到日志中的密码。
#shell> grep ‘temporary password’ /var/log/mysqld.log
#首次通过初始密码登录后，使用以下命令修改密码, 或使用 mysql_secure_installation命令也可以按提示修改
shell> mysql -uroot -p
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘NewPassword’;

##问题汇总：
1. mysql 5.7 默认安装了密码安全检查插件（validate_password），默认密码检查策略要求密码必须包含：大小写字母、数字和特殊符号，并且长度不能少于8位。否则会提示 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 错误
#解决方法，将密码强度改为LOW
#0 or LOW #1 or MEDIUM #2 or STRONG
#在mysql终端下运行：
mysql> set global validate_password_policy=0;
#默认都要求密码8位以上，可以使用下列命令修改长度为4
mysql> set global validate_password_length=4;

2. telnet mysql时出现：is not allowed to connect to this MySQL serverConnection closed by foreign host
#解决方法
#在mysql终端下运行：（%表示所有ip，如果要求本机适用localhost或ip地址代替）
mysql> grant all privileges on *.* to ‘root’@’%’ identified by ‘123456’;
#使授权生效
mysql> flush privileges;

3. mysql 安装后默认不支持中文，需要修改编码。
#修改 /etc/my.cnf 配置文件，在相关节点（没有则自行添加）下添加编码配置，如下：

[mysqld]
character-set-server=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#重启mysql服务，查询编码。可以看到已经改过来了

shell> systemctl restart mysqld
shell> mysql -uroot -p
mysql> show variables like ‘character%’;

 

 

 

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的最大好处就是可以将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代码  

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

查看MySQL配置文件路径(附my.cnf详解)

 mysql  查看MySQL配置文件路径(附my.cnf详解)已关闭评论
11月 122015
 

知识积累下。

(1)关于配置文件路径

有时候，我发现虽然尝试修改了配置文件的一些变量，但是并没有生效。后来才发现原来是因为修改的文件并非MySQL服务器读取的配置文件。

如果不清楚MySQL当前使用的配置文件路径，可以尝试这样查看:

从上图可以看出, 服务器首先会读取/etc/my.cnf文件，如果发现该文件不存在，再依次尝试从后面的几个路径进行读取。


(2)关于配置文件配置项分段

配置文件my.cnf通常会分成好几部分，如[client]，[mysqld], [mysql]等等。MySQL程序通常是读取与它同名的分段部分，例如服务器mysqld通常读取[mysqld]分段下的相关配置项。如果配置项位置不正确，该配置是不会生效的。


(3)关于全局变量

另外，如果想查看MySQL的一些全局变量设置，在非登录并有权限情况下可以这样查看: mysqladmin variables -p，

这个操作也就相当于登录时使用命令 show global variables;

附:   my.cnf详解

#BEGIN CONFIG INFO
#DESCR: 4GB RAM, 只使用InnoDB, ACID, 少量的连接, 队列负载大
#TYPE: SYSTEM
#END CONFIG INFO
 
#
# 此mysql配置文件例子针对4G内存。 
# 主要使用INNODB
#处理复杂队列并且连接数量较少的mysql服务器
#
# 将此文件复制到/etc/my.cnf 作为全局设置,
# mysql-data-dir/my.cnf 作为服务器指定设置
# (@localstatedir@ for this installation) 或者放入
# ~/.my.cnf 作为用户设置.
#
# 在此配置文件中, 你可以使用所有程序支持的长选项.
# 如果想获悉程序支持的所有选项
# 请在程序后加上“–help“参数运行程序.
#
# 关于独立选项更多的细节信息可以在手册内找到
#
 
#
# 以下选项会被MySQL客户端应用读取.
# 注意只有MySQL附带的客户端应用程序保证可以读取这段内容.
# 如果你想你自己的MySQL应用程序获取这些值
# 需要在MySQL客户端库初始化的时候指定这些选项
 
#
[client]
#password = [your_password]
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
 
# *** 应用定制选项 ***
 
#
#  MySQL 服务端
#
[mysqld]
 
# 一般配置选项
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@
 
# back_log 是操作系统在监听队列中所能保持的连接数,
# 队列保存了在MySQL连接管理器线程处理之前的连接.
# 如果你有非常高的连接率并且出现“connection refused“ 报错,
# 你就应该增加此处的值.
# 检查你的操作系统文档来获取这个变量的最大值.
# 如果将back_log设定到比你操作系统限制更高的值,将会没有效果
back_log = 50
 
# 不在TCP/IP端口上进行监听.
# 如果所有的进程都是在同一台服务器连接到本地的mysqld,
# 这样设置将是增强安全的方法
# 所有mysqld的连接都是通过Unix sockets 或者命名管道进行的.
# 注意在windows下如果没有打开命名管道选项而只是用此项
# (通过 “enable-named-pipe“ 选项) 将会导致mysql服务没有任何作用!
#skip-networking
 
# MySQL 服务所允许的同时会话数的上限
# 其中一个连接将被SUPER权限保留作为管理员登录.
# 即便已经达到了连接数的上限.
max_connections = 100
# 每个客户端连接最大的错误允许数量,如果达到了此限制.
# 这个客户端将会被MySQL服务阻止直到执行了“FLUSH HOSTS“ 或者服务重启
# 非法的密码以及其他在链接时的错误会增加此值.
# 查看 “Aborted_connects“ 状态来获取全局计数器.
max_connect_errors = 10
 
# 所有线程所打开表的数量.
# 增加此值就增加了mysqld所需要的文件描述符的数量
# 这样你需要确认在[mysqld_safe]中 “open-files-limit“ 变量设置打开文件数量允许至少4096
table_cache = 2048
 
# 允许外部文件级别的锁. 打开文件锁会对性能造成负面影响
# 所以只有在你在同样的文件上运行多个数据库实例时才使用此选项(注意仍会有其他约束!)
# 或者你在文件层面上使用了其他一些软件依赖来锁定MyISAM表
#external-locking
 
# 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要)
# 每个连接独立的大小.大小动态增加
max_allowed_packet = 16M
 
# 在一个事务中binlog为了记录SQL状态所持有的cache大小
# 如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能.
# 所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中
# 如果事务比此值大, 会使用磁盘上的临时文件来替代.
# 此缓冲在每个连接的事务第一次更新状态时被创建
binlog_cache_size = 1M
 
# 独立的内存表所允许的最大容量.
# 此选项为了防止意外创建一个超大的内存表导致永尽所有的内存资源.
max_heap_table_size = 64M
 
# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序
# 如果排序后的数据无法放入排序缓冲,
# 一个用来替代的基于磁盘的合并分类会被使用
# 查看 “Sort_merge_passes“ 状态变量.
# 在排序发生时由每个线程分配
sort_buffer_size = 8M
 
# 此缓冲被使用来优化全联合(full JOINs 不带索引的联合).
# 类似的联合在极大多数情况下有非常糟糕的性能表现,
# 但是将此值设大能够减轻性能影响.
# 通过 “Select_full_join“ 状态变量查看全联合的数量
# 当全联合发生时,在每个线程中分配
join_buffer_size = 8M
 
# 我们在cache中保留多少线程用于重用
# 当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size,
# 则客户端线程被放入cache中.
# 这可以在你需要大量新连接的时候极大的减少线程创建的开销
# (一般来说如果你有好的线程模型的话,这不会有明显的性能提升.)
thread_cache_size = 8
 
# 此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.
# 此值只对于支持 thread_concurrency() 函数的系统有意义( 例如Sun Solaris).
# 你可可以尝试使用 [CPU数量]*(2..4) 来作为thread_concurrency的值
thread_concurrency = 8
 
# 查询缓冲常被用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果.
# 打开查询缓冲可以极大的提高服务器速度, 如果你有大量的相同的查询并且很少修改表.
# 查看 “Qcache_lowmem_prunes“ 状态变量来检查是否当前值对于你的负载来说是否足够高.
# 注意: 在你表经常变化的情况下或者如果你的查询原文每次都不同,
# 查询缓冲也许引起性能下降而不是性能提升.
query_cache_size = 64M
 
# 只有小于此设定值的结果才会被缓冲
# 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖.
query_cache_limit = 2M
 
# 被全文检索索引的最小的字长.
# 你也许希望减少它,如果你需要搜索更短字的时候.
# 注意在你修改此值之后,
# 你需要重建你的 FULLTEXT 索引
ft_min_word_len = 4
 
# 如果你的系统支持 memlock() 函数,你也许希望打开此选项用以让运行中的mysql在在内存高度紧张的时候,数据在内存中保持锁定并且防止可能被swapping out
# 此选项对于性能有益
#memlock
 
# 当创建新表时作为默认使用的表类型,
# 如果在创建表示没有特别执行表类型,将会使用此值
default_table_type = MYISAM
 
# 线程使用的堆大小. 此容量的内存在每次连接时被预留.
# MySQL 本身常不会需要超过64K的内存
# 如果你使用你自己的需要大量堆的UDF函数
# 或者你的操作系统对于某些操作需要更多的堆,
# 你也许需要将其设置的更高一点.
thread_stack = 192K
 
# 设定默认的事务隔离级别.可用的级别如下:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ
 
# 内部(内存中)临时表的最大大小
# 如果一个表增长到比此值更大,将会自动转换为基于磁盘的表.
# 此限制是针对单个表的,而不是总和.
tmp_table_size = 64M
 
# 打开二进制日志功能.
# 在复制(replication)配置中,作为MASTER主服务器必须打开此项
# 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志.
log-bin=mysql-bin
 
# 如果你在使用链式从服务器结构的复制模式 (A->B->C),
# 你需要在服务器B上打开此项.
# 此选项打开在从线程上重做过的更新的日志,
# 并将其写入从服务器的二进制日志.
#log_slave_updates
 
# 打开全查询日志. 所有的由服务器接收到的查询 (甚至对于一个错误语法的查询)
# 都会被记录下来. 这对于调试非常有用, 在生产环境中常常关闭此项.
#log
 
# 将警告打印输出到错误log文件.  如果你对于MySQL有任何问题
# 你应该打开警告log并且仔细审查错误日志,查出可能的原因.
#log_warnings
 
# 记录慢速查询. 慢速查询是指消耗了比 “long_query_time“ 定义的更多时间的查询.
# 如果 log_long_format 被打开,那些没有使用索引的查询也会被记录.
# 如果你经常增加新查询到已有的系统内的话. 一般来说这是一个好主意,
log_slow_queries
 
# 所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询.
# 不要在这里使用“1“, 否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL 目前时间的精确度只能达到秒的级别).
long_query_time = 2
 
# 在慢速日志中记录更多的信息.
# 一般此项最好打开.
# 打开此项会记录使得那些没有使用索引的查询也被作为到慢速查询附加到慢速日志里
log_long_format
 
# 此目录被MySQL用来保存临时文件.例如,
# 它被用来处理基于磁盘的大型排序,和内部排序一样.
# 以及简单的临时表.
# 如果你不创建非常大的临时文件,将其放置到 swapfs/tmpfs 文件系统上也许比较好
# 另一种选择是你也可以将其放置在独立的磁盘上.
# 你可以使用“;“来放置多个路径
# 他们会按照roud-robin方法被轮询使用.
#tmpdir = /tmp
 
 
# ***  复制有关的设置
 
 
# 唯一的服务辨识号,数值位于 1 到 2^32-1之间.
# 此值在master和slave上都需要设置.
# 如果 “master-host“ 没有被设置,则默认为1, 但是如果忽略此选项,MySQL不会作为master生效.
server-id = 1
 
# 复制的Slave (去掉master段的注释来使其生效)
#
# 为了配置此主机作为复制的slave服务器,你可以选择两种方法:
#
# 1) 使用 CHANGE MASTER TO 命令 (在我们的手册中有完整描述) –
#    语法如下:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    你需要替换掉 <host>, <user>, <password> 等被尖括号包围的字段以及使用master的端口号替换<port> (默认3306).
#
#    例子:
#
#    CHANGE MASTER TO MASTER_HOST=‘125.564.12.1‘, MASTER_PORT=3306,
#    MASTER_USER=‘joe’, MASTER_PASSWORD=‘secret’;
#
# 或者
#
# 2) 设置以下的变量. 不论如何, 在你选择这种方法的情况下, 然后第一次启动复制(甚至不成功的情况下,
#     例如如果你输入错密码在master-password字段并且slave无法连接),
#    slave会创建一个 master.info 文件,并且之后任何对于包含在此文件内的参数的变化都会被忽略
#    并且由 master.info 文件内的内容覆盖, 除非你关闭slave服务, 删除 master.info 并且重启slave 服务.
#    由于这个原因,你也许不想碰一下的配置(注释掉的) 并且使用 CHANGE MASTER TO (查看上面) 来代替
#
# 所需要的唯一id号位于 2 和 2^32 – 1之间
# (并且和master不同)
# 如果master-host被设置了.则默认值是2
# 但是如果省略,则不会生效
#server-id = 2
#
# 复制结构中的master – 必须
#master-host = <hostname>
#
# 当连接到master上时slave所用来认证的用户名 – 必须
#master-user = <username>
#
# 当连接到master上时slave所用来认证的密码 – 必须
#master-password = <password>
#
# master监听的端口.
# 可选 – 默认是3306
#master-port = <port>
 
# 使得slave只读.只有用户拥有SUPER权限和在上面的slave线程能够修改数据.
# 你可以使用此项去保证没有应用程序会意外的修改slave而不是master上的数据
#read_only
 
 
#*** MyISAM 相关选项
 
 
# 关键词缓冲的大小, 一般用来缓冲MyISAM表的索引块.
# 不要将其设置大于你可用内存的30%,
# 因为一部分内存同样被OS用来缓冲行数据
# 甚至在你并不使用MyISAM 表的情况下, 你也需要仍旧设置起 8-64M 内存由于它同样会被内部临时磁盘表使用.
key_buffer_size = 32M
 
# 用来做MyISAM表全表扫描的缓冲大小.
# 当全表扫描需要时,在对应线程中分配.
read_buffer_size = 2M
 
# 当在排序之后,从一个已经排序好的序列中读取行时,行数据将从这个缓冲中读取来防止磁盘寻道.
# 如果你增高此值,可以提高很多ORDER BY的性能.
# 当需要时由每个线程分配
read_rnd_buffer_size = 16M
 
# MyISAM 使用特殊的类似树的cache来使得突发插入
# (这些插入是,INSERT  SELECT, INSERT  VALUES (), (), , 以及 LOAD DATA
# INFILE) 更快. 此变量限制每个进程中缓冲树的字节数.
# 设置为 0 会关闭此优化.
# 为了最优化不要将此值设置大于 “key_buffer_size“.
# 当突发插入被检测到时此缓冲将被分配.
bulk_insert_buffer_size = 64M
 
# 此缓冲当MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一个空表中引起重建索引时被分配.
# 这在每个线程中被分配.所以在设置大值时需要小心.
myisam_sort_buffer_size = 128M
 
# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).
# 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)
myisam_max_sort_file_size = 10G
 
# 如果被用来更快的索引创建索引所使用临时文件大于制定的值,那就使用键值缓冲方法.
# 这主要用来强制在大表中长字串键去使用慢速的键值缓冲方法来创建索引.
myisam_max_extra_sort_file_size = 10G
 
# 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.
# 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.
myisam_repair_threads = 1
 
# 自动检查和修复没有适当关闭的 MyISAM 表.
myisam_recover
 
 
# 默认关闭 Federated
skip-federated
 
# *** BDB 相关选项 ***
 
# 如果你运行的MySQL服务有BDB支持但是你不准备使用的时候使用此选项. 这会节省内存并且可能加速一些事.
skip-bdb
 
 
# *** INNODB 相关选项 ***
 
# 如果你的MySQL服务包含InnoDB支持但是并不打算使用的话,
# 使用此选项会节省内存以及磁盘空间,并且加速某些部分
#skip-innodb
 
# 附加的内存池被InnoDB用来保存 metadata 信息
# 如果InnoDB为此目的需要更多的内存,它会开始从OS这里申请内存.
# 由于这个操作在大多数现代操作系统上已经足够快, 你一般不需要修改此值.
# SHOW INNODB STATUS 命令会显示当先使用的数量.
innodb_additional_mem_pool_size = 16M
 
# InnoDB使用一个缓冲池来保存索引和原始数据, 不像 MyISAM.
# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
# 注意在32位系统上你每个进程可能被限制在 2–3.5G 用户层面内存限制,
# 所以不要设置的太高.
innodb_buffer_pool_size = 2G
 
# InnoDB 将数据保存在一个或者多个数据文件中成为表空间.
# 如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了.
# 其他情况下.每个设备一个文件一般都是个好的选择.
# 你也可以配置InnoDB来使用裸盘分区 – 请参考手册来获取更多相关内容
innodb_data_file_path = ibdata1:10M:autoextend
 
# 设置此选项如果你希望InnoDB表空间文件被保存在其他分区.
# 默认保存在MySQL的datadir中.
#innodb_data_home_dir = <directory>
 
# 用来同步IO操作的IO线程的数量. This value is
# 此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好.
innodb_file_io_threads = 4
 
# 如果你发现InnoDB表空间损坏, 设置此值为一个非零值可能帮助你导出你的表.
# 从1开始并且增加此值知道你能够成功的导出表.
#innodb_force_recovery=1
 
# 在InnoDb核心内的允许线程数量.
# 最优值依赖于应用程序,硬件以及操作系统的调度方式.
# 过高的值可能导致线程的互斥颠簸.
innodb_thread_concurrency = 16
 
# 如果设置为1 ,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上,
# 这提供了完整的ACID行为.
# 如果你愿意对事务安全折衷, 并且你正在运行一个小的食物, 你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O
# 0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.
# 2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.
innodb_flush_log_at_trx_commit = 1
 
# 加速InnoDB的关闭. 这会阻止InnoDB在关闭时做全清除以及插入缓冲合并.
# 这可能极大增加关机时间, 但是取而代之的是InnoDB可能在下次启动时做这些操作.
#innodb_fast_shutdown
 
# 用来缓冲日志数据的缓冲区的大小.
# 当此值快满时, InnoDB将必须刷新数据到磁盘上.
# 由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)
 
innodb_log_buffer_size = 8M
 
# 在日志组中每个日志文件的大小.
# 你应该设置日志文件总合大小到你缓冲池大小的25%~100%
# 来避免在日志文件覆写上不必要的缓冲池刷新行为.
# 不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间.
innodb_log_file_size = 256M
 
# 在日志组中的文件总数.
# 通常来说2~3是比较好的.
innodb_log_files_in_group = 3
 
# InnoDB的日志文件所在位置. 默认是MySQL的datadir.
# 你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能
#innodb_log_group_home_dir
 
# 在InnoDB缓冲池中最大允许的脏页面的比例.
# 如果达到限额, InnoDB会开始刷新他们防止他们妨碍到干净数据页面.
# 这是一个软限制,不被保证绝对执行.
innodb_max_dirty_pages_pct = 90
 
# InnoDB用来刷新日志的方法.
# 表空间总是使用双重写入刷新方法
# 默认值是 “fdatasync“, 另一个是 “O_DSYNC“.
#innodb_flush_method=O_DSYNC
 
# 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久.
# InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务.
# 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎
# 那么一个死锁可能发生而InnoDB无法注意到.
# 这种情况下这个timeout值对于解决这种问题就非常有帮助.
innodb_lock_wait_timeout = 120
 
 
[mysqldump]
# 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项
quick
 
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
 
# 仅仅允许使用键值的 UPDATEs 和 DELETEs .
#safe-updates
 
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
 
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
 
[mysqlhotcopy]
interactive-timeout
 
[mysqld_safe]
# 增加每个进程的可打开文件数量.
# 警告: 确认你已经将全系统限制设定的足够高!
# 打开大量表需要将此值设b
open-files-limit = 8192

转自：http://blog.csdn.net/moxiaomomo/article/details/9792801

从一个死锁看mysql innodb的锁机制

 mysql  从一个死锁看mysql innodb的锁机制已关闭评论
11月 122015
 

网上一篇关于mysql 死锁分析的现象分析，分享下

背景及现象

         线上生产环境在某些时候经常性的出现数据库操作死锁，导致业务人员无法进行操作。经过DBA的分析，是某一张表的insert操作和delete操作发生了死锁。简单介绍下数据库的情况（因为涉及到真实数据，这里做了模拟，不影响具体的分析和分析的结果。）假设存在如下2张表：




 Order 表的数据如下：

 


 Customer表的数据如下：


 

Order和Customer 在实体关系上存在一个关联，即order实体拥有一个指向customer实体的指针。在数据库设计的时候，order表的customer_id没有被设计成一个外键，这是因为在对order表做操作的时候不希望外键影响数据库的操作性能。这里把对外键约束的检查放到了应用程序里面，即数据库仅仅当成一个持久化和事务性的保证。同时为了查询方便，对customer_id做了索引。

 

在这个模拟业务场景中存在一个业务（因为是模拟的，所以不关心现实中是不是正确），一个客户拥有的订单会经常性的发生变化。即这个客户可能删除他名下的一些已经存在的订单，又增加一些新的订单，或者修改一些存在的订单，这3种操作可能都会发生在一个请求中。这时，应用人员做了一个不太好的实现：当一个客户把他这次改动的订单传到后台以后。开发人员不管这次有没有发生变化都针对这个客户的订单进行了先delete后insert的操作，来替代update操作。这里的实现是可以理解的，因为这一次请求中的订单可能需要delete,insert和update 三种操作，这样就要分辨出这批从页面传入的数据那些是delete,那些是insert,那些是upadte,还不如统一做成先delete再insert操作。

 

因为上面业务场景的实现的关系，抽象出来的一个事务中的数据库操作如下：

Start transaction; // 开事务

Delete from `order` where customer_id = XXX;  // 先删除XXX名下所有订单

Insert into ‘order’ (customer_id) values (xxx);   // 再Inset多条XXX名下的订单

Insert into ‘order’ (customer_id) values (xxx);

Insert into ‘order’ (customer_id) values (xxx);

……….

Commit; //事务提交

 

这样的操作在高并发的情况下，经常性的出现数据库死锁。

 

假设我们进行如下2个事务的操作（客户3和客户5都想增加一条自己的订单记录）：

 

T1 ：

 



 T2：


如果在mysql服务器端，执行顺序如下：

T1  Start Transaction ;

T2  Start Transaction ;

T1   delete from `order` where customer_id = 3;

T2   delete from `order` where customer_id = 5;

T1  insert into `order` (customer_id) values (3);

T2  insert into `order` (customer_id) values (5);

…….

这个时候，T1 insert 语句没有办法执行，一直在等待一个锁授权。Mysql 的锁信息如下：


 

 Thread 5 尝试在 insert 的时候在等待一个锁授权，已经等待了10秒。可以看到事务0 10248 持有了2个锁；事务0 10247 有2个锁，1个等待锁授权。整个数据库只有这2个事务，所以导致insert等待的锁一定被0 10248持有了。

 

如果 T2 的insert语句继续执行，那么死锁就发生了，mysql的信息如下：


 

分析

         首先我们先要了解下基本的数据库的锁的知识。

         数据库为了提高并发性，对于读和写进行2种不同的锁控制，分别称为共享锁（S锁）和排他锁（X锁）。这两种锁不是mysql独有的，在一般性的数据库基本原理介绍中都会提到。同时还有相应的意向锁的概念。

在mysql的innodb 存储引擎里面，使用的是行锁（S，X），以及表锁（IS，IX）。这里4种锁有个兼容矩阵（兼容矩阵做什么用的？不需要解释了吧，可以参考数据库基本原理的书）如下：



 我们打开锁监控，然后再具体观察下在事务执行之中的锁情况。

A   ：T1  Start Transaction ;

B   ：T2  Start Transaction ;

C   ：T1  delete from `order` where customer_id = 3;

D   ：T2  delete from `order` where customer_id = 5;

E   ：T1  insert into `order` (customer_id) values (3);

F   ：T2  insert into `order` (customer_id) values (5);

…….

我们先按照顺序执行到E，下面是mysql的锁情况：

T1



 T2


 

我们可以清楚的看到 T1 持有（包括等待授权的）3个锁：一个是对表order的IX锁；一个是对表order上面的index customer_id的 Gap类型的X锁； 还有一个是对表Order上面index customer_id 的 Insert intention 类型的X锁等待被授权。

T2 持有2个锁：一个是对表Order的IX锁； 一个是对表order上面的index customer_id 的Gap锁。

注意 T1 的Gap，Insert intention ,T2 的Gap 都是锁的同一个地方 “space id 0 page no 198 n bits 80”

 

这里介绍下mysql innodb下的锁类型：

常见的三种类型


拿上面的例子来说

Record 类型，简单的理解就是执行delete from `order` where id = 1,锁住的order表里面id =１的记录。

Gap 类型：简单的理解就是执行 delete from `order` where customer_id = 3。这里在order表里面没有customer_id=3 的记录。但是又由于customer_id存在一个索引，mysql根据索引进行搜索，索引的key是(1,2,6)，3不在这些key里面而是位于(2,6)之间的gap（间隙）中。Mysql对于(2,6)这个间隙加的锁就叫做Gap锁。这个例子中的间隙一共有(-∞,1),(1,2),(2,6),(6,+∞)这4个。注意gap只锁间隙不锁记录。

Next-Key 类型 : 简单的理解就是 Gap + 下一个 Record 。拿上面Gap的例子来说的话，锁住的就是(2,6]。这里包括了6这个记录。

 

除开以上三种常见的锁类型，还有一种对于Insert语句的特殊锁类型


 

也就是说insert语句会对插入的行加一个X锁，但是在插入这个行的过程之前，会设置一个Insert intention的Gap锁，叫做Insert intention锁。

以上面的例子来说，在执行 insert into `order` (customer_id) values (3)的时候，由于存在customer_id的索引，所以会对这个索引的(2,6)增加一个Insert Intention 类型的X锁。

 

了解了这些之后，我们回到上面的例子。

这里我们清楚的知道 –“注意 T1 的Gap，Insert intention ,T2 的Gap 都是锁的同一个地方 “space id 0 page no 198 n bits 80””—3个锁锁住同一个地方的原因了。因为customer_id = 3 和customer_id =5 都是属于同一个gap（2,6）。

T1 持有 gap (2,6) X锁，同时有个 insert intention (2,6)的X锁在等待gap(2,6)的X锁的释放；

T2 持有 gap(2,6) X锁。

这就是导致T1的insert 语句执行不下去的真正原因。 当T2的insert 语句执行的时候，（即F语句）可以预见，T2也会有个 insert intention(2,6)的X锁在等待gap(2,6)的X锁的释放。这样就形成了死锁。

        

         分析到这里就结束了么？好像那个地方有点不对。T1本身不就是拥有了一个gap(2,6)的X锁么？等等，为什么在T1拥有gap(2,6)X锁的情况下，T2还可以拥有gap(2,6)X锁？X锁同X锁不是不兼容的么（看看兼容矩阵）？

 

         是的，看看上面的兼容矩阵。IX与IX兼容，X与X不兼容。T1和T2 同时拥有对于表order的IX锁是可以理解的；但是T1和T2 同时拥有对于表order的index customer_id的X锁似乎就无法理解了。按照兼容矩阵的说法，在T2执行D语句的时候就应该被block，因为它需要获取Gap(2,6)的X锁，但是这个锁已经被T1执行C语句的时候持有了，所以只有在T1事务执行完以后，T2才能继续执行，按照这个顺序下来，是不会发生死锁的。

Mysql 或者说是 Innodb 是不是弄错了什么？

其实，我们分析的没有错，Mysql也没有弄错，唯一错的地方是官方文档上面没有介绍除了这个（IS,IX,S,X）的兼容矩阵外，在Mysql实现内部还有一个更加精确的被称为“precise mode”的兼容矩阵。（该矩阵没有出现在官方文档上，是有人通过Mysql lock0lock.c:lock_rec_has_to_wait源代码推测出来的。）下面这个是“precise mode”的兼容矩阵：（这个兼容矩阵发生在X与X，S与X不兼容的情况下再进行比对的）

 G    I     R    N (已经存在的锁,包括等待的锁)
  G   +     +    +     + 
  I    –      +    +     –
  R   +     +     –     –
  N   +     +     –     –
  + 代表兼容， -代表不兼容. I代表插入意图锁,
  G代表Gap锁，I代表插入意图锁,R代表记录锁，N代表Next-Key锁.

(http://www.mysqlops.com/2012/05/19/locks_in_innodb.html#more-3169)

这里需要注意的一点是，存在Insert Intention 锁时，申请Gap锁是允许的；但是存在Gap锁时，申请Insert Intention锁时是被阻止的。

        

         回到上面的例子，这下就可以解释清楚了。

         执行C语句完毕，T1持有了Gap(2,6)的X锁；

         执行D语句，T2 申请Gap(2,6)的X锁，根据“precise mode”兼容矩阵，该申请被授权，所以T2 持有了Gap(2,6)的X锁。

         执行E语句，T1 申请Insert Intention (2,6)的X锁，根据“precise mode”兼容矩阵，由于T2持有Gap(2,6)的X锁，该申请被T2 block。

         执行F语句，T2 申请 Insert Intention(2,6)的X锁，根据“precise mode”兼容矩阵，由于T1持有Gap(2,6)的X锁，该申请被T1 block。

         这里一个死锁很明显的出现，T1与T2都持有一个锁，同时都在等对方释放一个锁。到这里，整个死锁的原因分析清楚了。

 

解决

我们分析清楚了死锁形成的原因，就很好去解决这个问题了。可以看出T1，T2 都是持有了Gap 锁，等待insert intention被授权。

只要消除了Gap锁，这个死锁就解决了。方案有几种：

A delete 表 order上面的index customer_id。这样在delete的时候就不会产生Gap锁，insert 的时候也不会有insert intention锁。不过对于查询会有影响。

B 在delete的时候，不让事务获取到Gap锁。比如，在执行delete from        `order`  where customer_id = 3 ;之前，先通过数据库查询 select * from `order` where customer_id = 3; 看是否存在记录。不存在记录这不执行delete操作。因为insert总是要发生，delete则不是必须一定要发生的。

 

 

后记

         在真实解决线上这个问题的时候，走过了一些弯路，某些现象也让我认为是找到了真实的原因，其实那只是虚幻的假象。

         因为死锁发生在Insert 语句上面，一开始我们认为是`order` 表上面的主键id自增锁引起的（有点主观臆断，病急乱投医）。然后，我们把`order`上面的主键id转换成类似Oracle的sequence 序列，通过应用程序给予其赋值id。大家可以去尝试操作下，把一张表的主键id的auto_increment 给改掉，是多么恶心的一个操作(不是说多复杂，而是说这个操作的方式让有“操作洁癖”的人无法忍受)。等到上线以后，确实似乎好了很多，但是根源还是存在，只是它现在不想咬你。又过了段时间，系统压力上来了，这个问题又暴露出来了。正是应了那句“屋漏偏招连夜雨”，祸不单行，当问题出现的时候，开始我们还是认为是insert语句生成id的方式造成的，慢慢的对于这个问题的分析越来越详尽，终于意识到“id 生成方式”是替罪羔羊，真正的原因在于过多的无意义的delete操作的时候，这个问题才算是解决。

         为了避免大家对主键id自增锁的偏见，我简单介绍下主键id自增锁的机制，也算是我对冤枉它的一种补偿吧。

         主键自增锁基本上是通过 select Max(id) from table for update来实现的。很明显，for update 加的是表锁而且是X的。和其他的锁的区别就在于它的释放时机，其他的锁是跟随事务的。自增锁不跟着事务走，而是跟着那条Insert语句走。

         在Mysql 5.1.22版本以后，增加了 innodb_autoinc_lock_mode的参数，来调整主键自增锁的性能。这个时候不一定会进行锁表操作了，有可能就是直接在内存里面算好id值。在这种情况下面，mysql会对Insert语句进行分类，不同的分类在不同的参数 innodb_autoinc_lock_mode 下面会有不同的自增方式。大家可以参考《mysql技术内幕 InnoDB存储引擎》 。

 

转自：http://narcissusoyf.iteye.com/blog/1637309

 Older Entries