在家创业系统－网赚博客

1、对于MySQL来说，有三种锁的级别：页级、表级、行级。

页级的典型代表引擎为BDB。
表级的典型代表引擎为MyISAM,MEMORY以及很久以前的ISAM。
行级的典型代表引擎为INNODB。

2、我们实际应用中用的最多的就是行锁。
行级锁的优点如下：
1）、当很多连接分别进行不同的查询时减小LOCK状态。
2）、如果出现异常，可以减少数据的丢失。因为一次可以只回滚一行或者几行少量的数据。
行级锁的缺点如下：
1）、比页级锁和表级锁要占用更多的内存。
2）、进行查询时比页级锁和表级锁需要的I/O要多，所以我们经常把行级锁用在写操作而不是读操作。
3）、容易出现死锁。

3、MySQL用写队列和读队列来实现对数据库的写和读操作。

对于写锁定如下：
1）、如果表没有加锁，那么对其加写锁定。
2）、否则，那么把请求放入写锁队列中。
对于读锁定如下：
1）、如果表没有加写锁，那么加一个读锁。
2）、否则，那么把请求放到读锁队列中。
当然我们可以分别用low_priority 以及high_priority在写和读操作上来改变这些行为。

4、下面我来一个简单的例子解释上面的说法。

我们来运行一个时间很长的查询
1）、客户端1：
Sql代码 
mysql> select count(*) from content group by content;  
... 

mysql> select count(*) from content group by content;
...
客户端2：
Sql代码 
mysql> update content set content = 'I love you' where id = 444;  
Query OK, 1 row affected (30.68 sec)  
Rows matched: 1  Changed: 1  Warnings: 0 

mysql> update content set content = 'I love you' where id = 444;
Query OK, 1 row affected (30.68 sec)
Rows matched: 1  Changed: 1  Warnings: 0
用了半分钟。
2）、我们现在终止客户端1。
此时客户端2：
Sql代码 
mysql> update content set content = 'I hate you' where id = 444;  
Query OK, 1 row affected (0.02 sec)  
Rows matched: 1  Changed: 1  Warnings: 0 

mysql> update content set content = 'I hate you' where id = 444;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
仅仅用了20毫秒。

这个例子很好的说明了读写队列的运行。
对于1中的客户端1，此时表没有加锁，当然也没有加写锁了，那么此时客户端1对表加了一个读锁。
对于1中的客户端2，此时因为表有一个读锁，所以把UPDATE请求放到写锁定队列中。
当读锁释放的时候，也就是SHOW PROCESSLIST中STATUS 为COPY TO TMP TABLE的时候，UPDATE操作开始执行。

5、可以在REPLICATION中对MASTER 和SLAVE运用不同的锁定使系统达到最佳的性能。（当然这个前提是SQL语句都是最优的。）
 

Filed under:mysql
引用(0) 浏览(5)

mysql批量删除前缀相同的表：
Sql代码 
Select CONCAT( 'drop table ', table_name, ';' )  
FROM information_schema.tables  
Where table_name LIKE 'pre_%'; 
 

Filed under:mysql
引用(0) 浏览(5)

可以根据自己的需求自己改写：）

bash mysql_backup.sh 

 bash mysql_backup.sh

 
#!/bin/bash  
#This is a ShellScript For Auto DB Backup  
#Powered by liuzheng  
 
#系统变量定义  
DBName=Bulletin_production  
DBUser=root  
DBPasswd=  
BackupPath=/home/worker/mysql_backup/  
 
NewFile="$BackupPath"db$(date +%y%m%d).tar.gz  
DumpFile="$BackupPath"db$(date +%y%m%d).sql  
OldFile="$BackupPath"db$(date +%y%m%d --date='1 days ago').tar.gz  
 
 
#创建备份文件  
if [ ! -d $BackupPath ]; then  
    mkdir $BackupPath  
fi  
 
echo "---------------------------" 
echo $(date +"%y-%m-%d %H:%M:%S")   
echo "---------------------------"   
 
#删除历史文件  
if [ -f $OldFile ]; then  
　　rm -f $OldFile >> $LogFile  
  　echo "[$OldFile]Delete Old File Success!" 
else 
   echo "not exist old file!" 
fi  
 
#新文件  
if [ -f $NewFile ]; then  
    echo "[$NewFile] The Backup File is exists,Can't Backup! " 
else 
    mysqldump -u $DBUser  $DBName > $DumpFile  
    tar czvf $NewFile $DumpFile   
    rm -rf $DumpFile      
    echo "[$NewFile]Backup Success!" 
fi 
 

Filed under:mysql
引用(0) 浏览(145)

在我们数据中由于是根据当时的情况对客户表和客户分类表新增一个中间，又因为是中期新增，所以中间表的建表结构是将客户表的ID和客户分组表的ID分别写入到中间表里面，这样中间表就有三个字段主键客户表ID客户分组表ID，当时由于数据量大处理不过来所以没有建立级联，在客户表里面有许多导入数据的功能，也有删除数据的功能由于删除和导入操作频繁没有管理中间表的空间，致使中间表荣升到数据库第一大表快超过1达到1千万数据，苦于找方法瘦身，就找到了中间表的内容，删除当时删除客户资料没有删除中间表数据的内容：就有了以下的解决方案：

       利用sql语句删除：delete from m_customer_category where customer_sid_fk not in(select customer_sid from customer);

     由于customer表中的数据是680W，m_customer_category表数据是890w，上面的一条语句在服务器上执行了 3个小时后没有任何反应state状态是end一直是这样的情况，所以放弃没有进行删除。
修改下存储过程就可以实现单条删除数据了，根据这些条件即时不关闭数据的情况也可以正常的删除数据存储过程如下：

begin

              declare  coun1 int;

              declare   count2 int;

               select count(*) into coun1 from m_customer_category_back   limit  300000;

              while coun1 > 0 do

                     select   id into  count2 from m_customer_category_back LIMIT 1;

                     delete from m_customer_category where id = count2;

                     delete  from m_customer_category_back where id=count2;

                     SELECT  '删除一条'+count2;

                     commit;

                     set coun1=coun1-1;

              end while;

 

end

这是基本的SQL问题 :

换成如下语句试试：
delete from m_customer_category where not exists(select 1 from customer where m_customer_category.customer_sid_fk=customer_sid)

如果需要花费的时间还是很长，你可以将m_customer_category中的数据分成10次来执行。
如：delete from m_customer_category where ID>=1000000 and ID <200000 not exists(select 1 from customer where m_customer_category.customer_sid_fk=customer_sid)
 

Filed under:mysql
引用(0) 浏览(173)

select visit.*,pplist.id ppid from visit left join pplist on visit.orderserial=pplist.orderCode where visittime >= convert( '" + date1 + "',datetime) and visittime < INTERVAL 1 DAY + convert('" + date2 + "',datetime) and refurl like '%" + key + "%'；

上面是搜索语句，数据两太大，查询时间太长了，所以我想用临时表的方法来优化下，不知道大家有什么建议没

select visit.*,pplist.id ppid from visit,pplist where visit.orderserial=pplist.orderCode and visittime >= convert( '" + date1 + "',datetime) and visittime < INTERVAL 1 DAY + convert('" + date2 + "',datetime) and refurl like '%" + key + "%'

估计查询时间太长主要是那句like引起的。尽量少用like

Filed under:mysql
引用(0) 浏览(165)

更新该句
update da_question set answer_time = now()
where id in (
select id from da_question where  answer_time is null and id  in (select distinct parent_id from da_question)
)

在 MySQL 命令列工具中�回：
ERROR 1093 (HY000): You can't specify target table 'forum_members' for update in FROM clause

改成下列命令�理就 可以解决无法更新的问题
update da_question a inner join ( select id from da_question where  answer_time is null and id  in (select distinct parent_id from da_question)) b on a.id=b.id set answer_time=now();
 

Filed under:mysql
引用(0) 浏览(141)

以下createTime为datetime类型。

--更改所有数据年份为2007，但不修改日月时的SQL语句为：
update DateTimes
set createTime=dateadd(yyyy,datediff(yyyy,createTime,'2007'),createTime)

--更改某2007年的年份，改为2009年，但不修改日月时的SQL语句为：
update DateTimes
set createTime=dateadd(yyyy,datediff(yyyy,createTime,'2009'),createTime)
where createTime like '%2007%'

 

但有没有更好的方法，因为数据库中有多种年份，有没有方法一次把他们都改成要改的年份？

update tb set
createTime = date_format(createTime,'2007-%m-%d %H:%i:%s');

update
  DateTimes
set
  createTime=DATE_ADD(createTime,INTERVAL 2 Year)
where
  createTime like '%2007%';

Filed under:mysql
引用(0) 浏览(136)

下边的是关于在fleaphp中处理事务的

总结：在fleaphp中要支持事务。
1，改'driver'=>'mysqlt'，当然数据表为要innodb类型的
2.$dbo=FLEA::getDBO();
    $dbo->startTrans();
    中间写操作数据库的语句，判断是否执行成功
    如果都成功
    $dbo->completeTrans()
    如果有一条不成功
    $dbo->completeTrans(flase)
3.测试中故意
         $rowb=array('bd_id'=>'1','money'=>100000); //将primarykey的b_id写错，执行不成功，会出现错误提示，发现a中并没有插入数据，虽然
     $rowa=array('a_id'=>'1','money'=>1000000); $a->update($rowa);这些语句已经执行，但最终没要运行到提交，所以数据库中的数据并没有变
5.数据库结构
CREATE TABLE `a` (
    `a_id` int(11) NOT NULL auto_increment,
    `money` int(11) NOT NULL,
    PRIMARY KEY    (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=2 ;

INSERT INTO `a` (`a_id`, `money`) VALUES
(1, 100);

CREATE TABLE `b` (
    `b_id` int(11) NOT NULL auto_increment,
    `money` int(11) NOT NULL,
    PRIMARY KEY    (`b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=2 ;

INSERT INTO `b` (`b_id`, `money`) VALUES
(1, 100);

4。测试代码

<?php
error_reporting(E_ALL);
//引入fleaphp类
require_once('FLEA/FLEA.php');
//定义cache
FLEA::setAppinf('internalCacheDir',dirname(__FILE__).'/Cache');

//定义数据库连接参数
$dbDSN=array(
'driver'=>'mysqlt',    //为了启用事务，这里的driver要为mysqlt
'host'=>'127.0.0.1',
'login'=>'root',
'password'=>'123456',
'database'=>'test'
);
FLEA::setAppinf('dbDSN',$dbDSN);
FLEA::init();
FLEA::loadClass('FLEA_Db_TableDataGateway');
class Table_a extends FLEA_Db_TableDataGateway
{
var $tableName='a';
var $primaryKey='a_id';
}
class Table_b extends FLEA_Db_TableDataGateway
{
var $tableName='b';
var $primaryKey='b_id';
}

$a=FLEA::getSingleton('Table_a');
$b=FLEA::getSingleton('Table_b');
//得到数据库访问对象
$dbo=FLEA::getDBO();
//开始事务
$dbo->startTrans();
//修改表，让a表中的money-30,b表中的money+30,两者同时发生或不发生
$rowa=array(
        'a_id'=>'1',
     'money'=>70);
$a->update($rowa);
$rowb=array(
        'b_id'=>'1',
     'money'=>130);
$b->update($rowb);
//如果提交了，这里出理的结果与下边的一致，如果回滚两者会不一致
$rowa=$a->findAll();
dump($rowa);
$rowb=$b->findAll();
dump($rowb);
//$dbo->completeTrans();//如果两个操作都成功，提交
$dbo->completeTrans(false); //否则滚回

//用于查看事务最终有没有提交.如果提交了会和上边的输出一样
$rowa=$a->findAll();
dump($rowa);
$rowb=$b->findAll();
dump($rowb);
?>
 

 

Filed under:mysql
引用(0) 浏览(165)

$sql ="SELECT TIME_To_SEC( NOW( ) ) - TIME_TO_SEC( time_at ) FROM lwjcount WHERE ip = '$ip'  ORDER BY 'TIME_To_SEC( NOW( ) )*2 - TIME_To_SEC( time_at )' ASC";
这句隔天就会出现负数

 

$sql = "SELECT abs( TIME_TO_SEC( NOW( ) ) - TIME_TO_SEC( time_at ) ) FROM lwjcount WHERE ip = '$ip' ORDER BY `abs( TIME_TO_SEC( NOW( ) ) - TIME_TO_SEC( time_at ) )` ASC ";

这句正常

Filed under:mysql
引用(0) 浏览(150)

在向表中插入数据的时候，经常遇到这样的情况：1. 首先判断数据是否存在； 2. 如果不存在，则插入；3.如果存在，则更新。

在 SQL Server 中可以这样处理：

   if not exists (select 1 from t where id = 1)
      insert into t(id, update_time) values(1, getdate())
   else
      update t set update_time = getdate() where id = 1
那么 MySQL 中如何实现这样的逻辑呢？别着急！MySQL 中有更简单的方法： replace into

replace into t(id, update_time) values(1, now());
或

replace into t(id, update_time) select 1, now();
replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

MySQL replace into 有三种形式：
1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_name=value, ...
前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

Filed under:mysql
引用(0) 浏览(125)