分类: MySQL

利用keepalived构建高可用MySQL-HA

没有评论

2012 年 03 月 29 日 at 下午 8:31分类:MySQL | NoSQL

本来是找相关Redis的HA方案的,结果找到这篇文章,发现很不错,记录下,方便下次查找

地址:点击查看

上篇文章说配置Redis的主从问题,但我现在想要配置Redis的master-master模式,然后借用keepalived来实现故障自动转移的功能,
但是配置了Redis的相关参数之后,根本就不能达到我想要的目的,不知道是什么原因,看了网上很多的文章,都出现了类似的情况。估计是
Redis现在还不支持这个功能吧 期待高手。

参考资料:
1、http://blog.csdn.net/doraeimo/article/details/7029832
2、http://baike.baidu.com/view/4175422.htm

数据库池连接代理服务器SQL Relay的安装配置使用

没有评论

2011 年 12 月 13 日 at 下午 7:50分类:Linux | MySQL | PHP | WEB开发

具体的SQL Relay是什么,我相信网上有很多很多的文章,你可以去百度或者是谷歌上面找。
不说废话了,开始安装SQL Relay,到 http://sqlrelay.sourceforge.net/ 去下载相应的软件
安装SQL Relay需要先安装Rudiments,至于为什么,因为这是它以来的gcc编译包。 在 http://sqlrelay.sourceforge.net/download.html 可以找到。
1、安装Rudiments:

# tar vxzf rudiments-0.34.tar.gz
# cd rudiments-0.34
# ./configure --prefix=/usr/local/rudiments
# make
# make install

至此,rudiments安装结束
2、安装SQL Relay:

# tar vxzf sqlrelay-0.43.tar.gz
# cd sqlrelay-0.43
# ./configure --prefix=/usr/local/sqlrelay --with-rudiments-prefix=/usr/local/rudiments --with-mysql-prefix=MySQL安装路径 --with-php-prefix=PHP安装路径
# make
# make install

安装结束,以上编译参数根据个人需要来设定,我用的PHP+MYSQL的环境,所以只需要这两个东西!
安装结束之后会再PHP的extension_dir目录生成一个sql_relay.so文件,这个就是编译生成的SQL Relay的php拓展,我们需要把它加到php.ini文件中。
然后重启php-fpm,查看下phpinfo,看下是否加载了sql_relay拓展!
安装结束之后,就是如何使用了,在我们安装的/usr/local/sqlrelay/share/doc目录下面,是关于sql_relay的PHP的API以及配置参数,全部都在这个目录下面,只不过全是英文的,看起来很吃力啊。
下面是我根据提供的API封装的一个CLASS,

<?php
/**
 *@desc 数据访问层的操作
 *@author fbbin
 */
define('DB_USER',               'fbbin' );
define('DB_PASSWD',             'binbin' );
define('DB_ID',                 'router' );
define('DB_PORT',               9090 );
define('DB_SOCKET',             '/tmp/router.socket' );
define('DB_RETRYTIME',          0 );
define('DB_TRIES',              1 );
define('DB_BUFFER_SIZE',        0 );
define('DB_TIMEOUT',            10 );
define('DB_TRANS_AUTO_COMMIT',  FALSE );
define('DB_OK',                 TRUE );
define('DB_FAIL',               FALSE );
define('DB_DEBUG',              true );
define('DB_CHARSET',            'utf8' );
define('DB_SAVEQUERY',          TRUE );
define('DB_CACHE',              false );
define('DB_CACHE_TTL',          600 );
define('DB_CACHE_PATH',         './' );

class SqlrelayAction
{
	var $connection = '';
	var $cursor = '';
	var $affectedrows = '';
	var $errorNum = -1;
	var $queries = array();
	var $queryCount = 0;
	var $transStatus = true;
	
	/**
	 * construct
	 */
	public function __construct()
	{
		if( !$this->isSupport() )
		{
			$this->errorNum = 4;
			if( DB_DEBUG )
			{
				exit($this->getError());
			}
			return DB_FAIL;
		}
		sqlrcon_setTimeout( DB_TIMEOUT );
		if( !is_resource($this->connection) || !is_object($this->connection) )
		{
			$this->_dbConnect();
		}
		return $this->_init();
	}
	
	/**
	 * @desc 应用初始化,检测数据库是否启动等
	 * @access protected
     * @return void
	 */
	protected function _init()
	{
		$_init = sqlrcon_ping( $this->connection );
		if ( !$_init )
		{
			$this->errorNum = 0;
			if( DB_DEBUG )
			{
				exit($this->getError());
			}
			return DB_FAIL;
		}
		$this->checkTrans();
		$this->_initCursor();
		$this->setCharset();
	}
	
	/**
	 * @desc 创建数据池链接
	 * @access protected
     * @return bool
	 */
	protected function _dbConnect()
	{
		$this->connection = sqlrcon_alloc(DB_ID, DB_PORT, DB_SOCKET, DB_USER, DB_PASSWD, DB_RETRYTIME, DB_TRIES);
		return true;
	}
	
	/**
	 * @desc 获取操作句柄
	 * @access protected
     * @return bool
	 */
	protected function _initCursor()
	{
		$this->cursor = sqlrcur_alloc( $this->connection );
		return true;
	}
	
	/**
	 * @desc 预查询准备
	 * @access protected
     * @return void
	 */
	protected function _preQuery()
	{
		if( DB_CACHE )
		{
			$this->cacheOn( $query );
		}
		//将结果集存入缓冲区
		sqlrcur_setResultSetBufferSize($this->cursor, DB_BUFFER_SIZE);
	}
	
	/**
	 * @desc 执行一条SQL语句
	 * @param  $query 查询的SQL
	 * @access public
     * @return void
	 */
	public function query( $query = '' )
	{
		if( empty($query) )
		{
			$this->errorNum = 1;
			return DB_FAIL;
		}
		$isSelect = $this->isSelect($query);
		if( $isSelect && DB_CACHE )
		{
			$tmp = sqlrcur_openCachedResultSet($this->cursor, MD5($query));
			if( $tmp )
			{
				return $this->fetchAssoc();
			}
		}
		//
		$this->_preQuery( $query );
		
		if( DB_SAVEQUERY )
		{
			$this->queries[] = $query;
		}
		$this->queryCount++ ;
		
		if ( !sqlrcur_sendQuery($this->cursor, $query) ) {
			$this->affectedrows = 0;
			if( DB_DEBUG )
			{
				echo sqlrcur_errorMessage( $this->cursor );
				sqlrcur_free( $this->cursor );exit;
			}
			$this->transStatus = false;
			return DB_FAIL;
		}
		$this->affectedrows = $isSelect ? 0 : sqlrcur_affectedRows( $this->cursor );
		if( DB_CACHE )
		{
			$this->cacheOff();
		}
		if ( $isSelect ) {
			return $this->fetchAssoc();
		}
		return $this->affectedrows;
	}
	
	/**
	 * @desc 遍历查询的结果集
	 * @access protected
     * @return array
	 */
	protected function fetchAssoc()
	{
		$tmp = array();
		for($row = 0, $count = sqlrcur_rowCount($this->cursor); $row < $count; $row++)
		{
			$tmp[] = sqlrcur_getRowAssoc($this->cursor, $row);
		}
		
		if( sqlrcon_getDebug($this->connection) )
		{
			sqlrcon_debugOff( $this->connection );
		}
		sqlrcur_free( $this->cursor );
		
		return $tmp;
	}
	
	/**
	 * @desc 启动事务检测
	 * @access protected
     * @return bool
	 */
	protected function checkTrans()
	{
		if ( DB_TRANS_AUTO_COMMIT ) 
		{
            sqlrcon_autoCommitOn( $this->connection );
        } 
        else 
        {
            //sqlrcon_autoCommitOff( $this->connection );
        }
        return DB_OK;
	}
	
	/**
	 * @desc 启动事务
	 * @access public
     * @return bool
	 */
	public function transStart()
	{
		$this->transStatus = true;
		return sqlrcon_autoCommitOff( $this->connection );;
	}
	
	/**
	 * @desc 提交事务
	 * @access public
     * @return bool
	 */
	public function transCommit()
	{
		if( sqlrcon_commit($this->connection) == 1 )
		{
			return DB_OK;
		}
		else
		{
			$this->errorNum = 2;
			return DB_FAIL;
		}
	}
	
	/**
	 * @desc 获取操作的事务状态
	 * @access public
     * @return bool
	 */
	public function transStatus()
	{
		return $this->transStatus;
	}
	
	/**
	 * @desc 事务回滚
	 * @access public
     * @return bool
	 */
	public function transRollback()
	{
		if( sqlrcon_rollback($this->connection) == 1 )
		{
			return DB_OK;
		}
		else 
		{
			$this->errorNum = 3;
			return DB_FAIL;
		}
	}
	
	/**
	 * @desc 开启SQL Relay缓冲
	 * @param  $query 查询的SQL
	 * @access protected
     * @return bool
	 */
	protected function cacheOn($query = '')
	{
		sqlrcur_cacheToFile($this->cursor, DB_CACHE_PATH.MD5($query));

		sqlrcur_setCacheTtl($this->cursor, DB_CACHE_TTL);
		
		return true;
	}
	
	/**
	 * @desc 关闭SQL Relay缓冲
	 * @access protected
     * @return void
	 */
	protected function cacheOff()
	{
		sqlrcur_cacheOff( $this->cursor );
	}
	
	/**
	 * @desc 设置查询编码
	 * @access protected
     * @return void
	 */
	protected function setCharset()
	{
		return sqlrcur_sendQuery($this->cursor, "set names " . DB_CHARSET);
	}
	
	/**
	 * @desc 检测系统是否支持SQL Relay
	 * @access protected
     * @return bool
	 */
	protected function isSupport()
	{
		if( extension_loaded('sql_relay') && function_exists( 'sqlrcon_alloc' ) )
		{
			return true;
		}
		else 
		{
			return false;
		}
	}
	
	/**
	 * @desc 挂起结果集,挂起会话
	 * @access public
     * @return resoure
	 */
	public function suspendResult()
	{
		sqlrcur_suspendResultSet($this->cursor);
		sqlrcon_suspendSession($this->connection);
		$return['ResultSetId'] = sqlrcur_getResultSetId($this->cursor);
		$return['PortId']      = sqlrcon_getConnectionPort($this->connection);
		$return['SocketId']    = sqlrcon_getConnectionSocket($this->connection);
        return $return;
	}
	
	/**
	 * @desc 恢复结果集,恢复会话
	 * @access public
     * @return bool
	 */
	public function resumeResult( $params )
	{
		if( !$this->connection )
		{
			$this->_dbConnect();
		}
		if( !$this->cursor )
		{
			$this->_initCursor();
		}
		//恢复会话
		sqlrcon_resumeSession($this->connection, $params['PortId'], $params['SocketId'] );
		return sqlrcur_resumeResultSet($this->connection, $params['ResultSetId']);
	}
	
	/**
	 * @desc 检测一条SQL是否是SELECT语句,并发送字段信息与否
	 * @access protected
     * @return bool
	 */
	protected function isSelect( $query = '' )
	{
		$is = ( preg_match('/^\s*SHOW TABLES\s*/si', $query) || preg_match('/^\s*\(?\s*SELECT\s+/si', $query) ) &&
                    !preg_match('/^\s*\(?\s*SELECT\s+INTO\s+/si', $query);
		//告诉服务器是否发送字段信息
		if( $is )
		{
			sqlrcur_getColumnInfo( $this->cursor );
		}
		else 
		{
			sqlrcur_dontGetColumnInfo( $this->cursor );
		}
		return $is;
	}
	
	/**
	 * @desc 获取SQL操作影响的行数
	 * @access public
     * @return intval
	 */
	public function affectedRows()
	{
		return $this->affectedrows;
	}
	
	/**
	 * @desc 结果集的列数
	 * @access public
     * @return intval
	 */
	public function colNums()
    {
        return sqlrcur_colCount($this->cursor);
    }
	
    /**
     * @desc 返回最后一条SQL
     * @access public
     * @return string
     */
    public function lastQuery()
    {
    	return end($this->queries);
    }
    
    /**
     * @desc 获取debug调试信息
     * @access public
     * @return string
     */
    public function showDebugInfo()
    {
    	return sqlrcon_debugOn($this->connection);
    }
    
    /**
     * @desc 返回数据操作的相关版本信息
     * @access public
     * @return array
     */
    public function version()
    {
    	$dbtype = sqlrcon_identify( $this->connection );
    	return array($dbtype.' version'=>sqlrcon_dbVersion( $this->connection ),
    				'SQL Relay server version'=>sqlrcon_serverVersion( $this->connection ),
    				'SQL Relay client version'=>sqlrcon_clientVersion( $this->connection )
    				);
    }
    
    /**
     * @desc 结果集的行数
     * @access public
     * @return intval
     */
	public function rowNums()
    {
        return sqlrcur_rowCount($this->cursor);
    }
    
	/**
	 * @desc 获取错误信息
	 * @access public
     * @return array
	 */
	public function getErrorInfo()
	{
		$errorInfo = array('The Database is down!',
						   'The Sql you commit was empty!',
						   'The Transaction commit fail!',
						   'The Transaction rollback fail!',
						   'The System do not support SQL Relay action!');
		if( $this->errorNum >=0 )
		{
			return $errorInfo[$this->errorNum];
		}
		else {
			return sqlrcur_errorMessage( $this->cursor );
		}
	}
	
	/**
	 * @desc 释放系统资源
	 */
	public function __destruct()
	{
		sqlrcon_endSession($this->connection);
		sqlrcon_free($this->connection);
        $this->connection = null;
        $this->queryCount = 0;
        return DB_OK;
	}
	
}

$obj = new SqlrelayAction();
//$obj->showDebugInfo();
$sel = "update dk_users set `usr_lastname`='fbbin001'";
$up = "select * from `dk_users`";
$arr = $obj->query( $up );

echo $obj->queryCount;

print_r($arr);

?>

这个些都是很简单的一些东西,但是由于是英文的文档,看得我相当吃力啊,花费了不少时间啊,其实主要的功能是在它的一个实现读写分离技术上面,这就需要看他额配置文件,配置文件位于/usr/lcoal/sqlrelay/etc/sqlrelay.conf,下面是我的配置文件:

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>

	<!-- Regular SQL Relay Instance -->
	<instance id="reader" port="9091" socket="/tmp/reader.socket" dbase="mysql" connections="6" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="listener" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no" timequeriessec="-1" timequeriesusec="-1">
		<users>
			<user user="fbbin" password="binbin"/>
		</users>
		<connections>
			<connection connectionid="reader_one" string="host=127.0.0.1;user=fbbin;password=binbin;db=slave;port=3306;charset=utf8" metric="1" behindloadbalancer="no"/>
			<connection connectionid="reader_two" string="host=127.0.0.1;user=fbbin;password=binbin;db=slave_two;port=3306;charset=utf8" metric="1" behindloadbalancer="no"/>
		</connections>
	</instance>

	<instance id="writer" port="9092" socket="/tmp/writer.socket" dbase="mysql" connections="4" maxconnections="15" maxqueuelength="5" growby="2" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="listener" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no" timequeriessec="-1" timequeriesusec="-1">
		<users>
			<user user="fbbin" password="binbin"/>
		</users>
		<connections>
			<connection connectionid="writer_one" string="host=127.0.0.1;user=fbbin;password=binbin;db=master;port=3306;charset=utf8" metric="1" behindloadbalancer="no"/>
			<connection connectionid="writer_two" string="host=127.0.0.1;user=fbbin;password=binbin;db=master_two;port=3306;charset=utf8" metric="1" behindloadbalancer="no"/>
		</connections>
	</instance>

	<instance id="router" port="9090" socket="/tmp/router.socket" dbase="router" connections="3" maxconnections="10" maxqueuelength="5" growby="2" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="listener" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no">
		<users>
			<user user="fbbin" password="binbin"/>
		</users>
		<router>
			<route host="127.0.0.1" port="9092" socket="/tmp/writer.socket" user="fbbin" password="binbin">
				<query pattern="^\s*insert\s+into\s+"/>
				<query pattern="^\s*INSERT\s+INTO\s+"/>
                                <query pattern="^\s*update\s+"/>
				<query pattern="^\s*UPDATE\s+"/>
                                <query pattern="^\s*delete\s+from\s+"/>
				<query pattern="^\s*DELETE\s+FROM\s+"/>
                                <query pattern="^\s*drop\s+table\s+"/>
				<query pattern="^\s*DROP\s+TABLE\s+"/>
                                <query pattern="^\s*create\s+table\s+"/>
				<query pattern="^\s*CREATE\s+TABLE\s+"/>
                        </route>
                        <route host="127.0.0.1" port="9091" socket="/tmp/reader.socket" user="fbbin" password="binbin">
				<query pattern="^\s*select\s+.*\s+from\s+"/>
				<query pattern="^\s*SELECT\s+.*\s+FROM\s+"/>
				<query pattern=".*"/>
                        </route>    
                </router>
	</instance>

</instances>

这里呢就模拟实现了数据的读写分离,至于这里面的XML的tag所对应的是什么意思,那么就需要去看看文档,我也是研究半天才弄出来的,不容易啊 !其实能写到这里很多都是参考了网上的一些资料,虽然很多资料不全,很多资料吵来吵去的,哎,这就需要自己去整理了。

参考资料:
1、http://www.linuxsir.org/main/?q=node/144;
2、http://www.ydmsh.com/www/Blog/Show/id/152/;
3、http://www.ydmsh.com/www/Blog/Show/id/149/
4、更多的是看/usr/local/sqlrelay/share/doc下面的英文文档了

mysql的事务

没有评论

2011 年 11 月 08 日 at 下午 2:14分类:MySQL

先来明确一下事务涉及的相关知识:
事务都应该具备ACID特征。所谓ACID是Atomic(原子性),Consistent(一致性),Isolated(隔离性),Durable(持续性)四个词的首字母所写,下面以“银行转帐”为例来分别说明一下它们的含义:
原子性:组成事务处理的语句形成了一个逻辑单元,不能只执行其中的一部分。换句话说,事务是不可分割的最小单元。比如:银行转帐过程中,必须同时从一个帐户减去转帐金额,并加到另一个帐户中,只改变一个帐户是不合理的。
一致性:在事务处理执行前后,数据库是一致的。也就是说,事务应该正确的转换系统状态。比如:银行转帐过程中,要么转帐金额从一个帐户转入另一个帐户,要么两个帐户都不变,没有其他的情况。
隔离性:一个事务处理对另一个事务处理没有影响。就是说任何事务都不可能看到一个处在不完整状态下的事务。比如说,银行转帐过程中,在转帐事务没有提交之前,另一个转帐事务只能处于等待状态。
持续性:事务处理的效果能够被永久保存下来。反过来说,事务应当能够承受所有的失败,包括服务器、进程、通信以及媒体失败等等。比如:银行转帐过程中,转帐后帐户的状态要能被保存下来。
再来看看哪些问题会用到事务处理:

这里不说“银行转帐”的例子了,说一个大家实际更容易遇到的“网上购书”的例子。先假设一下问题的背景:网上购书,某书(数据库编号为123)只剩最后一本,而这个时候,两个用户对这本书几乎同时发出了购买请求,让我们看看整个过程:
在具体分析之前,先来看看数据表的定义:

create table book
(
    book_id unsigned int(10) not null auto_increment,
    book_name varchar(100) not null,
    book_price float(5, 2) not null, #我假设每本书的价格不会超过999.99元
    book_number int(10) not null,
    primary key (book_id)
)
type = innodb; #engine = innodb也行

对于用户甲来说,他的动作稍微比乙快一点点,其购买过程所触发的动作大致是这样的:

1. SELECT book_number FROM book WHERE  book_id = 123;
book_number大于零,确认购买行为并更新book_number
2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;
购书成功

而对于用户乙来说,他的动作稍微比甲慢一点点,其购买过程所触发的动作和甲相同:

1. SELECT book_number FROM book WHERE  book_id = 123;
这个时候,甲刚刚进行完第一步的操作,还没来得及做第二步操作,所以book_number一定大于零
2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;
购书成功

表面上看甲乙的操作都成功了,他们都买到了书,但是库存只有一本,他们怎么可能都成功呢?再看看数据表里book_number的内容,已经变成“-1”了,这当然是不能允许的(实际上,声明这样的列类型应该加上unsigned的属性,以保证其不能为负,这里是为了说明问题所以没有这样设置)
好了,问题陈述清楚了,再来看看怎么利用事务来解决这个问题,打开MySQL手册,可以看到想用事务来保护你的SQL正确执行其实很简单,基本就是三个语句:开始,提交,回滚。
——————————————————————————-
开始:START TRANSACTION或BEGIN语句可以开始一项新的事务
提交:COMMIT可以提交当前事务,是变更成为永久变更
回滚:ROLLBACK可以回滚当前事务,取消其变更
此外,SET AUTOCOMMIT = {0 | 1}可以禁用或启用默认的autocommit模式,用于当前连接。
——————————————————————————-
那是不是只要用事务语句包一下我们的SQL语句就能保证正确了呢?比如下面代码:

BEGIN;
SELECT book_number FROM book WHERE  book_id = 123;
// ...
UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;
COMMIT;

答案是否定了,这样依然不能避免问题的发生,如果想避免这样的情况,实际应该如下:

BEGIN;
SELECT book_number FROM book WHERE  book_id = 123 FOR UPDATE;
// ...
UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;
COMMIT;

由于加入了FOR UPDATE,所以会在此条记录上加上一个行锁,如果此事务没有完全结束,那么其他的事务在使用SELECT … FOR UPDATE请求的时候就会处于等待状态,直到上一个事务结束,它才能继续,从而避免了问题的发生,需要注意的是,如果你其他的事务使用的是不带FOR UPDATE的SELECT语句,将得不到这种保护。
实际在开发过程中,我们基本上不会自己去写一些事务的sql语句,比如我们在用CI框架时,里面有自带的事务处理机制:

$this->db->trans_start();
$this->db->query('a sql');
$this->db->query('anthor sql');
$this->db->query('.....');
$this->db->trans_complete();
if($this->db->trans_status === false){
    $this->db->trans_rollback();//执行失败,回滚(返回到操作之前的数据)
}

6个有用的MySQL语句

没有评论

2011 年 07 月 12 日 at 下午 11:23分类:MySQL

1. 计算年数
你想通过生日来计算这个人有几岁了。

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0; 

2. 两个时间的差
取得两个 datetime 值的差。假设 dt1 和 dt2 是 datetime 类型,其格式为 ‘yyyy-mm-dd hh:mm:ss’,那么它们之间所差的秒数为:

UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 )

除以60就是所差的分钟数,除以3600就是所差的小时数,再除以24就是所差的天数。

3. 显示某一列出现过N次的值

SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N; 

4. 计算两个日子间的工作日
所谓工作日就是除出周六周日和节假日。

SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN Start AND Stop
  AND DAYOFWEEK(d) NOT IN(1,7)
  AND holiday=0;

5. 查找表中的主键

 SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING (constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
  AND t.table_schema='db'
  AND t.table_name=tbl'
 

6. 查看你的数库有多大

 SELECT
  table_schema AS 'Db Name',
  Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)',
  Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'
FROM information_schema.tables
GROUP BY table_schema ;

CI中DB对象剖析

没有评论

2011 年 07 月 08 日 at 下午 5:01分类:MySQL

CI中使用DB类,只要你在你的控制器中使用了这句话:$this->load->database();那么你就可以在你的控制器中的任何地方或者是在模型的任何地方都可以使用$this->db->function()来操作数据库了。那么CI 是怎么样做到这一步的呢?下面一一道来:
首先,我做过测试,在$this->load->database()这句话之前你要是打印$this->db的话,是没有任何数据的,但是在这句话之后打印的话,那么会出现很多对象属性啊方法等信息。那么首先来看下Loader.php文件中的database方法:
该方法的主要功能有:1、通过get_instance()来获得一个CI对象,2、加载数据库初始化类文件database/DB.php,将初始化后的对象放置到$CI->db中,这样子,整个CI对象中就有了db对象了。在任何一个能获取CI对象的地方都可以使用db对象了。那么问题是:这个db对象到底是怎么了的呢?继续往下:
看下文件DB.php文件,里面就只有一个方法DB。这个方法的主要功能有:
1、加载config文件夹下面的数据库配置文件:config/database.php
2、判断当前选择的数据库,默认为$active_group = ‘default’
3、加载数据库驱动类文件/database/DB_driver.php
4、判断是否启动了Active Record 模式,如果是,那么就加载database/DB_active_rev.php文件,否则,不加载
5、判断类CI_DB是否存在,如果不存在,那么在AR模式下:class CI_DB extends CI_DB_active_record{};否则class CI_DB extends CI_DB_driver{}
6、加载指定数据库的驱动文件 如mysql的是:database/driver/mysql_driver.php
7、实例化database/driver/mysql_driver.php文件中类CI_DB_mysql_driver,这个实例化出来的对象就是我们平时用到的对象了。

类CI_DB_mysql_driver继承了CI_DB类,,然后CI_DB类 继承了CI_DB_active_record类,CI_DB_active_record类继承了CI_DB_driver类,
类CI_DB_driver是数据库的基类,
上面的继承都是在AR模式下面的
如果没有开启AR模式,那么继承方式是一样的;如下:
类CI_DB_mysql_driver继承了CI_DB类,,然后CI_DB类直接继承了CI_DB_driver类。
整个数据库的流程大致就是这样子。
//PS:
为什么我们在控制器和模型中都可以使用$this->db来操作数据库对象呢?
刚刚上面我们说了在控制器中的使用原理,那么在模型中为什么可以使用呢?
其实模型中的$this->db是完全靠控制器中的对象来实现的,主要是:
1、通过get_instance()来获取CI对象,
2、通过__get方法来获取db这个私有属性的值

function __get($key){
      return $CI->$key;
}

mysql命令行下的数据导入和把查询结果导出

没有评论

2011 年 03 月 10 日 at 上午 9:26分类:MySQL

导入数据:
load data local infile ” into table data
导出数据:
select phone,city,left(`phone`,3) from data left join l_mobile on left(`phone`,7) = left(`mobiletel`,7) where city = ‘杭州’ into outfile ‘D:\\hangz.csv’;

MySQL中concat函数

没有评论

2011 年 02 月 24 日 at 上午 11:42分类:MySQL | WEB开发

使用方法:
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);
+————————+
| concat(’11′,’22′,null) |
+————————+
| NULL |
+————————+
1 row in set (0.00 sec)

MySQL中concat_ws函数
使用方法:
CONCAT_WS(separator,str1,str2,…)

CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。
注意:
如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

如连接后以逗号分隔
mysql> select concat_ws(‘,’,’11′,’22′,’33′);

+——————————-+
| concat_ws(‘,’,’11′,’22′,’33′) |
+——————————-+
| 11,22,33 |
+——————————-+
1 row in set (0.00 sec)

和MySQL中concat函数不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL
mysql> select concat_ws(‘,’,’11′,’22′,NULL);
+——————————-+
| concat_ws(‘,’,’11′,’22′,NULL) |
+——————————-+
| 11,22 |
+——————————-+
1 row in set (0.00 sec)

MySQL中group_concat函数
完整的语法如下:
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

基本查询

mysql> select * from aa;
+——+——+
| id| name |
+——+——+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+——+——+
6 rows in set (0.00 sec)

以id分组,把name字段的值打印在一行,逗号分隔(默认)

mysql> select id,group_concat(name) from aa group by id;
+——+——————–+
| id| group_concat(name) |
+——+——————–+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+——+——————–+
3 rows in set (0.00 sec)

以id分组,把name字段的值打印在一行,分号分隔

mysql> select id,group_concat(name separator ‘;’) from aa group by id;
+——+———————————-+
| id| group_concat(name separator ‘;’) |
+——+———————————-+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+——+———————————-+
3 rows in set (0.00 sec)

以id分组,把去冗余的name字段的值打印在一行,

逗号分隔

mysql> select id,group_concat(distinct name) from aa group by id;
+——+—————————–+
| id| group_concat(distinct name) |
+——+—————————–+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+——+—————————–+
3 rows in set (0.00 sec)

以id分组,把name字段的值打印在一行,逗号分隔,以name排倒序

mysql> select id,group_concat(name order by name desc) from aa group by id;
+——+—————————————+
| id| group_concat(name order by name desc) |
+——+—————————————+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+——+—————————————+
3 rows in set (0.00 sec)

repeat()函数

用来复制字符串,如下’ab’表示要复制的字符串,2表示复制的份数

mysql> select repeat(‘ab’,2);

+—————-+
| repeat(‘ab’,2) |
+—————-+
| abab |
+—————-+

1 row in set (0.00 sec)

又如
mysql> select repeat(‘a’,2);

+—————+
| repeat(‘a’,2) |
+—————+
| aa |
+—————+
1 row in set (0.00 sec)

mysql向表中某字段后追加一段字符串:
update table_name set field=CONCAT(field,”,str)

mysql 向表中某字段前加字符串
update table_name set field=CONCAT(‘str’,field)

这个函数对你也许会有很大帮助哦!!

MySQL索引类型一览

没有评论

2011 年 02 月 17 日 at 上午 10:28分类:MySQL | WEB开发

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin。

在查找username=”admin”的记录 SELECT * FROM mytable WHERE username=’admin’;时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

MySQL索引类型包括:

(1)普通索引

这是最基本的索引,它没有任何限制。它有以下几种创建方式:

◆创建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

◆修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length)) ◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 删除索引的语法:

DROP INDEX [indexName] ON mytable;

(2)唯一索引

它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ◆创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

(3)主键索引

它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

(4)组合索引

为了形象地对比单列索引和组合索引,为表添加多个字段:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:

usernname,city,age usernname,city usernname 为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:

SELECT * FROM mytable WHREE username=”admin” AND city=”郑州” SELECT * FROM mytable WHREE username=”admin” 而下面几个则不会用到:

SELECT * FROM mytable WHREE age=20 AND city=”郑州” SELECT * FROM mytable WHREE city=”郑州”

(5)建立索引的时机

到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city=’郑州’ 此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

SELECT * FROM mytable WHERE username like’admin%’ 而下句就不会使用:

SELECT * FROM mytable WHEREt Name like’%admin’ 因此,在使用LIKE时应注意以上的区别。

(6)索引的不足之处

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

◆虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

(7)使用索引的注意事项

使用索引时,有以下一些技巧和注意事项:

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

◆不要在列上进行运算

select * from users where YEAR(adddate)<2007; 将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成 select * from users where adddate<‘2007-01-01’; ◆不使用NOT IN和<>操作

以上,就对其中MySQL索引类型进行了介绍。

如何建立Mysql索引

没有评论

2011 年 02 月 16 日 at 下午 3:55分类:MySQL | WEB开发

详见:1.http://www.360doc.com/content/10/1113/10/281812_68949035.shtml#

以下是为出现在where子句的字段建一个索引。为方便讲述,我们先建立一个如下的表。
  Code代码如下:
  CREATE TABLE mytable (
  id serial primary key,
  category_id int not null default 0,
  user_id int not null default 0,
  adddate int not null default 0
  );
  很简单吧,不过对于要说明这个问题,已经足够了。如果你在查询时常用类似以下的语句:
  SELECT * FROM mytable WHERE category_id=1;
  最直接的应对之道,是为category_id建立一个简单的索引:
  CREATE INDEX mytable_categoryid
  ON mytable (category_id);
  OK,搞定?先别高兴,如果你有不止一个选择条件呢?例如:
  SELECT * FROM mytable WHERE category_id=1 AND user_id=2;
  你的第一反应可能是,再给user_id建立一个索引。不好,这不是一个最佳的方法。你可以建立多重的索引。
  CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);
  注意到我在命名时的习惯了吗?我使用”表名_字段1名_字段2名”的方式。你很快就会知道我为什么这样做了。
  现在你已经为适当的字段建立了索引,不过,还是有点不放心吧,你可能会问,数据库会真正用到这些索引吗?测试一下就OK,对于大多数的数据库来说,这是很容易的,只要使用EXPLAIN命令:
  EXPLAIN
  SELECT * FROM mytable
  WHERE category_id=1 AND user_id=2;
  This is what Postgres 7.1 returns (exactly as I expected)
  NOTICE: QUERY PLAN:
  Index Scan using mytable_categoryid_userid on
  mytable (cost=0.00..2.02 rows=1 width=16)
  EXPLAIN
  以上是postgres的数据,可以看到该数据库在查询的时候使用了一个索引(一个好开始),而且它使用的是我创建的第二个索引。看到我上面命名的好处了吧,你马上知道它使用适当的索引了。

接着,来个稍微复杂一点的,如果有个ORDER BY字句呢?不管你信不信,大多数的数据库在使用order by的时候,都将会从索引中受益。
  SELECT * FROM mytable
  WHERE category_id=1 AND user_id=2
  ORDER BY adddate DESC;
  有点迷惑了吧?很简单,就象为where字句中的字段建立一个索引一样,也为ORDER BY的字句中的字段建立一个索引:
  CREATE INDEX mytable_categoryid_userid_adddate
  ON mytable (category_id,user_id,adddate);
  注意: “mytable_categoryid_userid_adddate” 将会被截短为
  ”mytable_categoryid_userid_addda”
  CREATE
  EXPLAIN SELECT * FROM mytable
  WHERE category_id=1 AND user_id=2
  ORDER BY adddate DESC;
  NOTICE: QUERY PLAN:
  Sort (cost=2.03..2.03 rows=1 width=16)
  -> Index Scan using mytable_categoryid_userid_addda
  on mytable (cost=0.00..2.02 rows=1 width=16)
  EXPLAIN
  看看EXPLAIN的输出,好象有点恐怖啊,数据库多做了一个我们没有要求的排序,这下知道性能如何受损了吧,看来我们对于数据库的自身运作是有点过于乐观了,那么,给数据库多一点提示吧。
  为了跳过排序这一步,我们并不需要其它另外的索引,只要将查询语句稍微改一下。这里用的是postgres,我们将给该数据库一个额外的提示–在ORDER BY语句中,加入where语句中的字段。这只是一个技术上的处理,并不是必须的,因为实际上在另外两个字段上,并不会有任何的排序操作,不过如果加入,postgres将会知道哪些是它应该做的。
  EXPLAIN SELECT * FROM mytable
  WHERE category_id=1 AND user_id=2
  ORDER BY category_id DESC,user_id DESC,adddate DESC;
  NOTICE: QUERY PLAN:
  Index Scan Backward using
  mytable_categoryid_userid_addda on mytable
  (cost=0.00..2.02 rows=1 width=16)
  EXPLAIN
  现在使用我们料想的索引了,而且它还挺聪明,知道可以从索引后面开始读,从而避免了任何的排序。

MySql count 去掉重复

没有评论

2011 年 02 月 15 日 at 下午 5:36分类:MySQL | WEB开发

1.

select count(distinct CName) from Course

2.

select count(CName) from (select distinct CName from Course) as temp

as可有可无。temp作为别名,若无则提示错误:1248 – Every derived table must have its own alias