Category Archives: Database

MySQL字符串函数substring：字符串截取

MySQL 字符串截取函数：left(), right(), substring(), substring_index()。还有 mid(), substr()。其中，mid(), substr() 等价于 substring() 函数，substring() 的功能非常强大和灵活。 1. 字符串截取：left(str, length) mysql> select left('example.com', 3); +————————-+ | left('example.com', 3) | +————————-+ | exa                     | +————————-+ 2. 字符串截取：right(str, length) mysql> select right('example.com', 3); +————————--+ | right('example.com', 3) | +————————--+ | com                      | +————————--+ 实例： #查询某个字段后两位字符 select right(last3, 2) as last2 from historydata limit 10; #从应该字段取后两位字符更新到另外一个字段 update historydata set last2=right(last3, 2); 3. 字符串截取：substring(str, pos); substring(str, pos, len) 3.1 从字符串的第 4 个字符位置开始取，直到结束。 mysql> select substring('example.com', 4); +——————————+ […]

MySql 里的IFNULL、NULLIF和ISNULL用法

今天用到了MySql里的isnull才发现他和MSSQL里的还是有点区别，现在简单总结一下： mysql中isnull,ifnull,nullif的用法如下： isnull(expr) 的用法： 如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。 mysql> select isnull(1+1); -> 0 mysql> select isnull(1/0); -> 1 使用= 的null 值对比通常是错误的。 isnull() 函数同 is null比较操作符具有一些相同的特性。请参见有关is null 的说明。 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。假设一个基于表达式的表的情况，     或MySQL必须在内存储器中储存一个临时表中IFNULL()的返回值： CREATE   TABLE   tmp   SELECT   IFNULL(1,’test')   AS   test； 在这个例子中，测试列的类型为   CHAR(4)。 NULLIF(expr1,expr2)  的用法：  如果expr1 =   expr2     成立，那么返回值为NULL，否则返回值为   expr1。这和CASE   WHEN   expr1   =   expr2 THEN   NULL   ELSE   expr1   END相同。 mysql>   SELECT NULLIF(1,1); […]

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.

一.问题描述  014-12-15 20:00:29 4398 [Warning] Unsafe statement written to the binary log using statement format since  BINLOG_FORMAT = STATEMENT. INSERT… ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO longxibendi (lxid, l_id, l_aplply, l_types, ctimes) VALUES  (75692, 218, 8, 2, 1418644829) ON DUPLICATE KEY UPDATE ctimes=1418644829 一个mysql master 的err log 报上面的错误。 二.问题原因 查了下原因，longxibendi这个表上有2个唯一键。则使用 INSERT… ON DUPLICATE KEY UPDATE   ，且当前数据库binlog_format是statement格式，这种sql语句就会报unsafe。 查了下手册 INSERT … ON DUPLICATE KEY UPDATE statements on tables with multiple primary or unique keys.  When executed against a table that contains more than one primary or unique key, this statement is considered unsafe, being sensitive to the order in which the storage engine checks the keys, which is not deterministic, and on which the choice of rows updated by the MySQL Server depends. An INSERT … ON DUPLICATE KEY UPDATE statement against a […]

mysql报错IP address could not be resolved解决方法

这个警告不会影响数据库的访问 但是当有大量的这种日志产生的时候，数据库之前的错误信息 就会很难去查询了。连接数越多，产生报警日志的频率越高。 一、错误描述 数据库的alert.log中，我们经常会出现下面的警告:

二、问题产生的原因 出现错误的原因是MYSQL Server在本地内存中维护了一个非本地的Client TCP cache，这个cache中包含了远程Client的登录信息，比如IP地址，hostname等信息。 如果Client连接到服务器后，Mysql首先会在本地TCP池中根据IP地址解析客户端的hostname或者反解析，如果解析不到，就会去DNS中进行解析，如果还是解析失败 就是在error log中写入这样的警告信息。 三、解决的办法： 1、修改配置文件 可以通过两个参数来disable这个功能，在MYSQL的配置文件中[mysqld]中加入下面的参数:

重新授权，将所有访问数据库服务器的授权方式都改成IP形式的。

2、添加授权。 将所有访问数据库服务器的授权方式都改成IP形式。 不同的用户用不同的用户名和密码。

然后去 mysql数据库下面的 user表  和db表 下面删除掉那些含有含有主机名字的权限记录。 四、总结 1、要么加上

使得MySQL将不再通过DNS解析地址。 2、赋予权限 要么在赋予权限的时候 直接用ip地址，去掉那些用主机名字的权限。 from:http://www.uedsc.com/ip-address-could-not-be-resolved.html

MySQL DATEDIFF() 函数

定义和用法 DATEDIFF() 函数返回两个日期之间的天数。 语法

date1 和 date2 参数是合法的日期或日期/时间表达式。 注释：只有值的日期部分参与计算。 实例 例子 1 使用如下 SELECT 语句：

结果： DiffDate 1 例子 2 使用如下 SELECT 语句：

结果： DiffDate -1   from:http://www.w3school.com.cn/sql/func_datediff_mysql.asp

MySql四舍五入

FLOOR(X) 返回不大于X的最大整数值。 mysql> select FLOOR(1.23); -> 1 mysql> select FLOOR(-1.23); -> -2 注意返回值被变换为一个BIGINT！ CEILING(X) 返回不小于X的最小整数值。 mysql> select CEILING(1.23); -> 2 mysql> select CEILING(-1.23); -> -1 注意返回值被变换为一个BIGINT！ ROUND(X) 返回参数X的四舍五入的一个整数。 mysql> select ROUND(-1.23); -> -1 mysql> select ROUND(-1.58); -> -2 mysql> select ROUND(1.58); -> 2 注意返回值被变换为一个BIGINT! ROUND(X,D) 返回参数X的四舍五入的有D为小数的一个数字。如果D为0，结果将没有小数点或小数部分。 mysql> select ROUND(1.298, 1); -> 1.3 mysql> select ROUND(1.298, 0); -> 1 注意返回值被变换为一个BIGINT! from:http://www.cnblogs.com/youyou/archive/2005/11/07/270783.html

Asp.net+Mysql,查询出错:由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败

在网上看到的解决方案基本都是说host文件中127.0.0.1 localhost 的对应， 但我查看了host文件，并没有问题，纠结半天，拿查询语句在Mysql Workbench上执行，报了一个错，说是子查询包含多个结果，我去~！这很简单的一个错误asp.net竟然没能准确反馈。 于是在子查询语句后面加limit 1，Ok~ 所以，出现“由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败”这个错误时，有可能是查询语句有问题，而不是连接有问题。 from:http://blog.csdn.net/idoiknow/article/details/8923553

MySQL中使用innobackupex、xtrabackup进行大数据的备份和还原教程

大数据量备份与还原，始终是个难点。当MYSQL超10G，用mysqldump来导出就比较慢了。在这里推荐xtrabackup,这个工具比mysqldump要快很多。 一、Xtrabackup介绍 1、Xtrabackup是什么 Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup的一个很好的替代品。 Xtrabackup有两个主要的工具：xtrabackup、innobackupex 1、xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表 2、 innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的 同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢 复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。 2、Xtrabackup可以做什么 : 在线(热)备份整个库的InnoDB、 XtraDB表 在xtrabackup的上一次整库备份基础上做增量备份(innodb only) 以流的形式产生备份，可以直接保存到远程机器上(本机硬盘空间不足时很有用) MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。 Xtrabackup工具支持对InnoDB存储引擎的增量备份，工作原理如下： (1)首先完成一个完全备份，并记录下此时检查点的LSN(Log Sequence Number)。 (2)在进程增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当前检查点的LSN。 首 先，在logfile中找到并记录最后一个checkpoint(“last checkpoint LSN”)，然后开始从LSN的位置开始拷贝InnoDB的logfile到xtrabackup_logfile；接着，开始拷贝全部的数据文 件.ibd；在拷贝全部数据文件结束之后，才停止拷贝logfile。 因为logfile里面记录全部的数据修改情况，所以，即时在备份过程中数据文件被修改过了，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致。 因为innobackupex支持innodb,myisam,所以本文说一下，怎么使用innobackupex。 二，安装xtrabackup 1、下载地址 http://www.percona.com/downloads/XtraBackup/ 2、安装 根据需求，选择不同的版本，我选择的是rpm安装包，如果报以下错误 复制代码代码如下: [root@localhost xtrabackup]# rpm -ivh percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm warning: percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY error: Failed dependencies: perl(Time::HiRes) is needed by percona-xtrabackup-2.2.4-5004.el6.x86_64 解决办法： 复制代码代码如下: [root@localhost xtrabackup]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL //安装依赖包 [root@localhost xtrabackup]# rpm -ivh percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm   //重新安装 warning: percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key […]

MySQL 数据备份与还原

一、数据备份 1、使用mysqldump命令备份 mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。 mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。 1、备份一个数据库 mysqldump基本语法： mysqldump -u username -p dbname table1 table2 …-> BackupName.sql 其中： dbname参数表示数据库的名称； table1和table2参数表示需要备份的表的名称，为空则整个数据库备份； BackupName.sql参数表设计备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库被分成一个后缀名为sql的文件； 使用root用户备份test数据库下的person表

其生成的脚本如下： 文件的开头会记录MySQL的版本、备份的主机名和数据库名。 文件中以“--”开头的都是SQL语言的注释，以"/*!40101"等形式开头的是与MySQL有关的注释。40101是MySQL数据库的版本号，如果MySQL的版本比1.11高，则/*!40101和*/之间的内容就被当做SQL命令来执行，如果比4.1.1低就会被当做注释。 2、备份多个数据库 语法：

加上了--databases选项，然后后面跟多个数据库

3、备份所有数据库 mysqldump命令备份所有数据库的语法如下：

示例：

2、直接复制整个数据库目录 MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。 不过在此之前，要先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。这种情况在开发环境可以，但是在生产环境中很难允许备份服务器。 注意：这种方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。 3、使用mysqlhotcopy工具快速备份 一看名字就知道是热备份。因此,mysqlhotcopy支持不停止MySQL服务器备份。而且，mysqlhotcopy的备份方式比mysqldump快。mysqlhotcopy是一个perl脚本，主要在Linux系统下使用。其使用LOCK TABLES、FLUSH TABLES和cp来进行快速备份。 原理：先将需要备份的数据库加上一个读锁，然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库，最后，把需要备份的数据库文件复制到目标目录。 命令格式如下：

dbname：数据库名称； backupDir：备份到哪个文件夹下； 常用选项： --help：查看mysqlhotcopy帮助； --allowold：如果备份目录下存在相同的备份文件，将旧的备份文件加上_old； --keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧的文件更名； --flushlog：本次辈分之后，将对数据库的更新记录到日志中； --noindices：只备份数据文件，不备份索引文件； --user=用户名：用来指定用户名，可以用-u代替； --password=密码：用来指定密码，可以用-p代替。使用-p时，密码与-p之间没有空格； --port=端口号：用来指定访问端口，可以用-P代替； --socket=socket文件：用来指定socket文件，可以用-S代替； mysqlhotcopy并非mysql自带，需要安装Perl的数据库接口包；下载地址为:http://dev.mysql.com/downloads/dbi.html 目前，该工具也仅仅能够备份MyISAM类型的表。 二、数据还原 　　1、还原使用mysqldump命令备份的数据库的语法如下： 　　mysql -u root -p [dbname] < backup.sq 示例：

2、还原直接复制目录的备份 　　通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制。 from:http://www.cnblogs.com/kissdodog/p/4174421.html

mysql数据库ibdata1文件瘦身

MYSQL运行2年之后ibdata1文件变的非常巨大，传说ibdata1是InnoDB的产物，而且只会增大不会减少。 上网搜了一下解决方法。大体思路就是备份数据，然后删除数据库再还原数据库。 # 备份数据库： mysqldump -uDBuser -pPassword --quick --force --routines --add-drop-database --all-databases --add-drop-table > /data/bkup/mysqldump.sql # 停止数据库 service mysqld stop # 删除这些大文件 rm /usr/local/mysql/var/ibdata1 rm /usr/local/mysql/var/ib_logfile* # 手动删除除Mysql之外所有数据库文件夹，然后启动数据库 service mysqld start # 还原数据 mysql -uDBuser -pPassword < /data/bkup/mysqldump.sql from:http://blog.sina.com.cn/s/blog_40ce02d7010169zr.html