Category Archives: Database

MySQL DATE_ADD() 函数

定义和用法 DATE_ADD() 函数向日期添加指定的时间间隔。 语法

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。 type 参数可以是下列值： Type 值 MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH 实例 假设我们有如下的表： OrderId ProductName OrderDate 1 'Computer' 2008-12-29 16:25:46.635 现在，我们希望向 "OrderDate" 添加 2 天，这样就可以找到付款日期。 我们使用下面的 SELECT 语句：

结果： OrderId OrderPayDate 1 2008-12-31 16:25:46.635   from:http://www.w3school.com.cn/sql/func_date_add.asp

mysql sql语句大全

1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server 创建 备份数据的 device USE master EXEC sp_addumpdevice disk , testBack , c:\mssql7backup\MyNwind_1.dat 开始 备份 BACKUP DATABASE pubs TO testBack 4、说明:创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表: A:create table tab_new like tab_old (使用旧表创建新表) B:create table tab_new as select col1,col2… from tab_old definition only 5、说明:删除新表 drop table tabname 6、说明:增加一个列 Alter table tabname add column col type 注:列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明:添加主键: Alter table tabname add primary key(col) 说明:删除主键: Alter table tabname drop primary key(col) 8、说明:创建索引:create [unique] index […]

Mysql双机热备实现

★预备知识 1.双机热备 对于双机热备这一概念，我搜索了很多资料，最后，还是按照大多数资料所讲分成广义与狭义两种意义来说。 从广义上讲，就是对于重要的服务，使用两台服务器，互相备份，共同执行同一服务。当一台服务器出现故障时，可以由另一台服务器承担服务任务，从而在不需要人工干预的情况下，自动保证系统能持续提供服务。 从狭义上讲，双机热备就是使用互为备份的两台服务器共同执行同一服务，其中一台主机为工作机(Primary Server)，另一台主机为备份主机(Standby Server)。在系统正常情况下，工作机为应用系统提供服务，备份机监视工作机的运行情况(一般是通过心跳诊断，工作机同时也在检测备份机是否正常)，当工作机出现异常，不能支持应用系统运营时，备份机主动接管工作机的工作，继续支持关键应用服务，保证系统不间断的运行。双机热备针对的是IT核心服务器、存储、网络路由交换的故障的高可用性解决方案。 2.为什么要进行双机热备? 双机热备服务针对的是服务器的故障。服务器的故障可能由各种原因引起，如设备故障、操作系统故障、软件系统故障等等。一般地讲，在技术人员在现场的情况下，恢复服务器正常可能需要10分钟、几小时甚至几天。从实际经验上看，除非是简单地重启服务器(可能隐患仍然存在)，否则往往需要几个小时以上。而如果技术人员不在现场，则恢复服务的时间就更长了。 而对于一些重要系统而言，用户是很难忍受这样长时间的服务中断的。因此，就需要通过双机热备服务，来避免长时间的服务中断，保证系统长期、可靠的服务。 当然，决定是否使用双机热备，正确的方法是要分析一下系统的重要性以及对服务中断的容忍程度，以些决定是否使用双机热备。换句话说，就是你的用户能容忍多长时间恢复服务，如果服务不能恢复会造成多大的影响。 在考虑双机热备时，需要注意，一般意义上的双机热备都会有一个切换过程，这个切换过程可能是一分钟左右。在切换过程中，服务是有可能短时间中断的。但是，当切换完成后，服务将正常恢复。因此，双机热备不是无缝、不中断的，但它能够保证在出现系统故障时，能够很快恢复正常的服务，业务不致受到影响。而如果没有双机热备，则一旦出现服务器故障，可能会出现几个小时的服务中断，对业务的影响就可能会造成很严重的损失。 3.双机热备技术与备份的概念区别 热备份指的是:High Available(HA)即高可用，而备份指的是Backup,即数据备份的一种，这是两种不同的概念，应对的产品也是两种功能上完全不同的产品。热备份主要保障业务的连续性，实现的方法是故障点的转移。而备份，主要目的是为了防止数据丢失，而做的一份拷贝，所以备份强调的是数据恢复而不是应用的故障转移。 4.双机热备方案的主要两种组建方式 双机热备方案在进行讨论的时候一定要考虑到很多的因素，其中在各种环境下应用的时候需要格外的引起注意。当然还是有主要的两方式可以借鉴考虑的。 第一种，双机热备它的工作原理是使用两台服务器，一台作为主服务器(Active)，运行应用系统来提供服务。另一台作为备机，安装完全一样的应用系统，但处于待机状态(Standby)。当Active服务器出现故障时，通过软件诊测将Standby机器激活，保证应用在短时间内完成恢复正常使用。 第二种，双机互备方式则是在双机热备的基础上，两个相对独立的应用在两台机器同时运行，但彼此均设为备机，当某一台服务器出现故障时，另一台服务器可以在短时间内将故障服务器的应用接管过来，从而保证了应用的持续性，这种方式实际上是双机热备方案的一种应用。 但目前使用最多的还是主从模式的双机热备方案。其大致表示可如下图所示: 目前基于存储共享的双机热备是双机热备方案的最标准方案。对于这种方式，采用两台服务器，使用共享的存储设备(磁盘阵列柜或存储区域网SAN)。两台服务器可以采用主从、互备等不同的方式。在工作过程中，两台服务器将以下一个虚拟的IP地址对外提供服务，依工作方式的不同，将服务请求发送给其中一台服务器承担。同时，服务器通过心跳线(目前往往采用建立私有网络的方式)侦测另一台服务器的工作状况。 下图即为双机热备工作大致状况图，如下图所示: 双机热备方案当一台服务器出现故障时，另一台服务器根据心跳侦测的情况做出判断，并进行切换，接管服务。对于用户而言，这一过程是全自动的，在很短时间内完成，从而不会对业务造成大的影响。由于使用共享的存储设备，因此两台服务器使用的实际上是一样的数据，由双机或集群软件对其进行管理。 5.Mysql双机热备实现原理图 有了上面对双机热备知识的讲解，对其实现原理就有了一个深入了解，那么我们要做mysql数据库的双机热备就清楚明了多了。 Mysql双机热备系统的拓扑结构如下图所示: 两台服务器通过以太网连接网络，通过网络对外提供服务、相互通信。 两台服务器之间用com口直接互联，双机热备软件利用这个连接进行双机热备相关的通信、监控和控制等。 两台服务器通过HBA卡连接FC网络，访问共同的磁盘阵列，实现双机热备系统必要的磁盘。 6.Mysql双机热备实现的配置 为了数据的安全，客户有两台机器作为互相备份，当一台机器出现故障时，自动切换到另一台服务器。大部分的软件是通过LifeKeeper来实现的，但是Mysql的双机备份在LifeKeeper里没有实现，所以只能自己手动来实现Mysql的双机备份了。 其实，Mysql的双机备份有一个很简单的第三方软件可以实现，那就是SQLyog，他有一个功能叫sja(SQLyog Job Agent)可以轻松实现，但是却有一个不足之处，就是Mysql表里必须有一个primary key，即主键值，如果没有，则此表不能用sja来实现。 第二种方法就是用Mysql自身的Replication机制来实现了。但是这个功能只有Mysql 3.23以上的版本才有。 这里先说明下，由于我还没有通过实际的应用例子来检测这种双机热备方式是否能过通过，所以我会在我通过实例实现后在续写我后面的关于Mysql双机热备实现的配置部分。现在这部分内容主要讲的还是双机热备份的实现原理和意义。   ★mysql双机热备的实现 接续上一篇关于mysql双机热备实现原理分析，在本文经过深思熟虑和多次用不同的方式实测试后。最后在这篇文章中，用一个小例子来完成mysql双机热备的实现。 Mysql数据库没有增量备份的机制，当数据量太大的时候备份是一个很大的问题。还好mysql数据库提供了一种主从备份的机制，其实就是把主数据库的所有的数据同时写到备份的数据库中。实现mysql数据库的热备份。 要想实现双机的热备，首先要了解主从数据库服务器的版本的需求。要实现热备mysql的版本都高于3.2。还有一个基本的原则就是作为从数据库的数据版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。 当然要实现mysql双机热备，除了mysql本身自带的REPLICATION功能可以实现外，也可以用Heartbeat这个开源软件来实现。不过本文主要还是讲如何用mysql自带的REPLICATION来实现mysql双机热备的功能。 1. 准备服务器 由于Mysql不同版本之间的(二进制日志)binlog格式可能会不太一样，因此最好的搭配组合是主(Master)服务器的Mysql版本和从(Slave)服务器版本相同或者更低，主服务器的版本肯定不能高于从服务器版本。 本次我用于测试的两台服务器版本都是Mysql-5.5.17。 2. Mysql 建立主-从服务器双机热备配置步骤 2.1环境描述 A服务器(主服务器Master):59.151.15.36 B服务器(从服务器Slave):218.206.70.146 主从服务器的Mysql版本皆为5.5.17 Linux环境下 将主服务器需要同步的数据库内容进行备份一份，上传到从服务器上，保证始初时两服务器中数据库内容一致。 不过这里说明下，由于我是利用Mysql在安装后就有的数据库test进行测试的，所以两台服务器里面是没有建立表的，只不分别在test里面建立了同样的一张空表tb_mobile; Sql语句如下: mysql> create table tb_mobile( mobile VARCHAR(20) comment 手机号码 , time timestamp DEFAULT now() comment 时间 ); 2.2 主服务器Master配置 2.2.1 创建同步用户 进入mysql操作界面，在主服务器上为从服务器建立一个连接帐户，该帐户必须授予REPLICATION SLAVE权限。因为从mysql版本3.2以后就可以通过REPLICATION对其进行双机热备的功能操作。 操作指令如下: mysql> grant replication slave on *.* to replicate @ 218.206.70.146 […]

mysql游标循环的使用

这里有一个比较坑的地方，注意，变量的定义不要和你的select的列的键同名！不然，fetch into 会失败！ 另外 ： 如果没有则插入数据，如果有则更新的方法： insert into test values (a,b) ON DUPLICATE KEY UPDATE a=c;   from:https://www.cnblogs.com/trying/p/3296793.html

mysql存储过程示例

DROP PROCEDURE IF EXISTS proc_name; delimiter // create definer=root@localhost procedure proc_name( in pi_id int ) begin todo…… end// delimiter ;

详解MySQL性能优化(二)

七、MySQL数据库Schema设计的性能优化 高效的模型设计 适度冗余-让Query尽两减少Join 大字段垂直分拆-summary表优化 大表水平分拆-基于类型的分拆优化 统计表-准实时优化 合适的数据类型 时间存储格式总类并不是太多，我们常用的主要就是DATETIME，DATE和TIMESTAMP这三种了。从存储空间来看TIMESTAMP最少，四个字节，而其他两种数据类型都是八个字节，多了一倍。而TIMESTAMP的缺点在于他只能存储从1970年之后的时间，而另外两种时间类型可以存放最早从1001年开始的时间。如果有需要存放早于1970年之前的时间的需求，我们必须放弃TIMESTAMP类型，但是只要我们不需要使用1970年之前的时间，最好尽量使用TIMESTAMP来减少存储空间的占用。 字符存储类型 CHAR[(M)]类型属于静态长度类型，存放长度完全以字符数来计算，所以最终的存储长度是基于字符集的，如latin1则最大存储长度为255字节，但是如果使用gbk则最大存储长度为510字节。CHAR类型的存储特点是不管我们实际存放多长数据，在数据库中都会存放M个字符，不够的通过空格补上，M默认为1。虽然CHAR会通过空格补齐存放的空间，但是在访问数据的时候，MySQL会忽略最后的所有空格，所以如果我们的实际数据中如果在最后确实需要空格，则不能使用CHAR类型来存放。 VARCHAR[(M)]属于动态存储长度类型，仅存占用实际存储数据的长度。TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT这四种类型同属于一种存储方式，都是动态存储长度类型，不同的仅仅是最大长度的限制。 事务优化 1. 脏读:脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。 2. 不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。 3. 幻读:是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。 Innodb在事务隔离级别方面支持的信息如下: 1.READ UNCOMMITTED 常被成为Dirty Reads(脏读)，可以说是事务上的最低隔离级别:在普通的非锁定模式下SELECT的执行使我们看到的数据可能并不是查询发起时间点的数据，因而在这个隔离度下是非Consistent Reads(一致性读); 2.READ COMMITTED 这一隔离级别下，不会出现DirtyRead，但是可能出现Non-RepeatableReads(不可重复读)和PhantomReads(幻读)。 3. REPEATABLE READ REPEATABLE READ隔离级别是InnoDB默认的事务隔离级。在REPEATABLE READ隔离级别下，不会出现DirtyReads，也不会出现Non-Repeatable Read，但是仍然存在PhantomReads的可能性。 4.SERIALIZABLE SERIALIZABLE隔离级别是标准事务隔离级别中的最高级别。设置为SERIALIZABLE隔离级别之后，在事务中的任何时候所看到的数据都是事务启动时刻的状态，不论在这期间有没有其他事务已经修改了某些数据并提交。所以，SERIALIZABLE事务隔离级别下，PhantomReads也不会出现。 八、可扩展性设计之数据切分 数据的垂直切分 数据的垂直切分，也可以称之为纵向切分。将数据库想象成为由很多个一大块一大块的“数据块”(表)组成，我们垂直的将这些“数据块”切开，然后将他们分散到多台数据库主机上面。这样的切分方法就是一个垂直(纵向)的数据切分。 垂直切分的优点 ◆数据库的拆分简单明了，拆分规则明确; ◆应用程序模块清晰明确，整合容易; ◆数据维护方便易行，容易定位; 垂直切分的缺点 ◆部分表关联无法在数据库级别完成，需要在程序中完成; ◆对于访问极其频繁且数据量超大的表仍然存在性能平静，不一定能满足要求; ◆事务处理相对更为复杂; ◆切分达到一定程度之后，扩展性会遇到限制; ◆过读切分可能会带来系统过渡复杂而难以维护。 数据的水平切分 数据的垂直切分基本上可以简单的理解为按照表按照模块来切分数据，而水平切分就不再是按照表或者是功能模块来切分了。一般来说，简单的水平切分主要是将某个访问极其平凡的表再按照某个字段的某种规则来分散到多个表之中，每个表中包含一部分数据。 水平切分的优点 ◆表关联基本能够在数据库端全部完成; ◆不会存在某些超大型数据量和高负载的表遇到瓶颈的问题; ◆应用程序端整体架构改动相对较少; ◆事务处理相对简单; ◆只要切分规则能够定义好，基本上较难遇到扩展性限制; 水平切分的缺点 ◆切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则; ◆后期数据的维护难度有所增加，人为手工定位数据更困难; ◆应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。 数据切分与整合中可能存在的问题 1.引入分布式事务的问题 完全可以将一个跨多个数据库的分布式事务分拆成多个仅处于单个数据库上面的小事务，并通过应用程序来总控各个小事务。当然，这样作的要求就是我们的俄应用程序必须要有足够的健壮性，当然也会给应用程序带来一些技术难度。 2.跨节点Join的问题 推荐通过应用程序来进行处理，先在驱动表所在的MySQLServer中取出相应的驱动结果集，然后根据驱动结果集再到被驱动表所在的MySQL Server中取出相应的数据。 3.跨节点合并排序分页问题 从多个数据源并行的取数据，然后应用程序汇总处理。 九、可扩展性设计之Cache与Search的利用 通过引入Cache(Redis、Memcached)，减少数据库的访问，增加性能。 通过引入Search(Lucene、Solr、ElasticSearch)，利用搜索引擎高效的全文索引和分词算法，以及高效的数据检索实现，来解决数据库和传统的Cache软件完全无法解决的全文模糊搜索、分类统计查询等功能。 以上就是本文的全部内容，希望大家可以喜欢。   from:http://www.jb51.net/article/70530.htm

MySQL 的 20+ 条最佳实践

数据库操作是当今 Web 应用程序中的主要瓶颈。 不仅是 DBA(数据库管理员)需要为各种性能问题操心，程序员为做出准确的结构化表，优化查询性能和编写更优代码，也要费尽心思。 在本文中，我列出了一些针对程序员的 MySQL 优化技术。 在我们开始学习之前，我补充一点:你可以在 Envato Market 上找到大量的 MySQL 脚本和实用程序。   1.优化查询的查询缓存 大部分MySQL服务器都有查询缓存功能。这是提高性能的最有效的方法之一，这是由数据库引擎私下处理的。当同一个查询被多次执行，结果会直接从缓存里提取，这样速度就很快。 主要的问题是，这对程序员来说太简单了，不容易看到，我们很多人都容易忽略。我们实际上是可以组织查询缓存执行任务的。 ? 1 2 3 4 5 6 // query cache does NOT work $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");   // query cache works! $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'"); 查询缓存在第一行不执行的原因在于CURDTE()功能的使用。这适用于所有的非确定性功能，就像NOW()和RAND()等等。。。因为功能返回的结果是可变的。MySQL决定禁用查询器的查询缓存。我们所需要做的是通过添加一额外一行PHP，在查询前阻止它发生。   2. EXPLAIN你的选择查询 使用EXPLAIN关键词可以帮助了解MySQL是怎样运行你的查询的。这有助于发现瓶颈和查询或表结构的其它问题。 EXPLAIN的查询结果会展示哪一个索引被使用过，表示怎样扫描和储存的，等等。。。 选择一个SELECT查询(一个有连接的复杂查询会更好)，在它的前面添加关键词EXPLAIN，这样就可以直接使用数据库了。结果会以一个漂亮的表来展示。例如，就好比我执行连接时忘了添加一栏的索引: 现在它只会从表2里面扫描9和16行，而非扫描7883行。经验法则是乘以所有“行”那一栏的数字，你的查询性能会跟结果数字成比例的。   3. 获取唯一行时使用LIMIT 1 有时当你查表时，你已经知道你正在查找的结果只有一行。你可能正在获取唯一记录，或者你可能只是查询是否存在满足你的WHERE子句条件的记录。 在这种情况下，将LIMIT 1添加到查询条件中可以提高性能。这样，数据库引擎将在找到刚刚第一个记录之后停止扫描记录，而不是遍历整个表或索引。 ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 // do I have any users from Alabama?   // what NOT to do: $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'"); if(mysql_num_rows($r) > 0) {     // … }     // much better: $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1"); if(mysql_num_rows($r) > 0) {     // … }   4. 索引搜索字段 索引不仅仅是为了主键或唯一键。如果你会在你的表中按照任何列搜索，你就都应该索引它们。 正如你所看到的，这个规则也适用于如 last_name LIKE a%' 的部分字符串搜索。当从字符串的开头搜索时，MySQL就可以使用那一列的索引。 你也应该明白什么样搜索可以不使用有规律的索引。例如，当搜索一个单词时(例如， WHERE post_content LIKE %apple%' […]

MySQL存储过程中游标循环的跳出和继续操作示例

最近遇到这样的问题,在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅. 1.REPEAT 复制代码代码如下: REPEAT Statements; UNTIL expression END REPEAT demo DECLARE num INT; DECLARE my_string  VARCHAR(255); REPEAT SET  my_string =CONCAT(my_string,num, , ); SET  num = num +1; UNTIL num <5 END REPEAT; 2.WHILE  复制代码代码如下: WHILE expression DO Statements; END WHILE demo DECLARE num INT; DECLARE my_string  VARCHAR(255); SET num =1; SET str = ; WHILE num  < span>10DO SET  my_string =CONCAT(my_string,num, , ); SET  num = num +1; END WHILE; 3.LOOP(这里面有非常重要的ITERATE,LEAVE) 复制代码代码如下: DECLARE num  INT; DECLARE str  VARCHAR(255); SET num =1; SET my_string = ; loop_label:  LOOP IF  […]

MySQL字符串连接函数

MySQL中 concat 函数 使用方法: CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。 注意: 如果所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。 一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况，可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS CHAR), char_col) MySQL的concat函数可以连接一个或者多个字符串,如 mysql> select concat( 10 ); + + | concat( 10 ) | + + | 10   | + + 1 row in set (0.00 sec) mysql> select concat( 11 , 22 , 33 ); + + | concat( 11 , 22 , 33 ) | + + | 112233 | + + 1 row in set (0.00 sec) MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL mysql> select concat( 11 , 22 ,null); + + […]

MySQL 数据库操作，将查询结果插入新表

做项目，需要测试数据，而现成的其他表有类似数据，为了方便，就将想将查询结果直接放到新表里。具体操作如下: INSERT INTO TableA(c1,c2,c3) SELECT TableB.c1,TableB.c2,TableB.c3 FROM TableB 参考:http://wenda.so.com/q/1372470880061763 如果表存在: insert into tab1 select * from tab2; 如果表不存在: create table tab1 as select * from tab2; from:http://blog.csdn.net/chark_leo/article/details/39525633