Category Archives: MySql

十二 28

MySQL语句：如果存在则更新，不存在则插入新记录

Posted on by

INSERT INTO … ON DUPLICATE KEY UPDATE …

表结构：

0
1
2
3
4
5
6
CREATE TABLE `mrdede_notfind` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `keyword` varchar(32) NOT NULL COMMENT '查询的关键词',
  `count` int(6) unsigned DEFAULT NULL COMMENT '计数：被查询的次数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `keyword` (`keyword`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='保存没有查询到的搜索关键词'

该语句是基于唯一索引或主键才可使用的，如下语句：

必要条件：其中字段id或keyword或count，至少有一个字段属性是主键或唯一索引。

0
INSERT INTO `mrdede_notfind` (id,keyword,count) VALUES (1,'织梦先生',3) ON DUPLICATE KEY UPDATE count=count+1;

语句解释：如果查询主键id=1或者唯一索引keyword=’织梦先生’的记录存在，则更新此记录的count字段 count=count+1；如果没有查询主键id=1或者唯一索引keyword=’织梦先生’的记录都不存在，则插入一行记录。

其它：

假定id=1的记录存在，则下面语句与上面语句是等效的

0
UPDATE table SET count=count+1 WHERE id=1 OR keyword='织梦先生';

 

十一 06

Python MySQL使用持久连接

Posted on by

python连接mysql中没有长连接的概念，但我们可以利用mysql的ping机制，来实现长连接功能~
思路:
1 python mysql 的cping 函数会校验连接的可用性,如果连接不可用将会产生异常
2 利用这一特性，构造一个连接丢失的循环，不断尝试连接数据库，直到连接恢复
3 使用这样的机制不需要关闭数据库功能,对于驻留进程，有大量数据进行写操作时，很有用途

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
#!/usr/bin/env python  
# -*-coding:UTF-8-*-  
import sys,MySQLdb,traceback  
import time  
class mysql:  
    def __init__ (self,  
                  host   = '',  
                  user   = '',  
                  passwd = '',  
                  db     = '',  
                  port   = 3306,  
                  charset= 'utf8'  
                  ):  
        self.host   = host  
        self.user   = user  
        self.passwd = passwd  
        self.db     = db  
        self.port   = port  
        self.charset= charset  
        self.conn   = None  
        self._conn()  
 
    def _conn (self):  
        try:  
            self.conn = MySQLdb.Connection(self.host, self.user, self.passwd, self.db, self.port , self.charset)  
            return True  
        except :  
            return False  
 
    def _reConn (self,num = 28800,stime = 3): #重试连接总次数为1天,这里根据实际情况自己设置,如果服务器宕机1天都没发现就......  
        _number = 0  
        _status = True  
        while _status and _number <= num:  
            try:  
                self.conn.ping()       #cping 校验连接是否异常  
                _status = False  
            except:  
                if self._conn()==True: #重新连接,成功退出  
                    _status = False  
                    break  
                _number +=1  
                time.sleep(stime)      #连接不成功,休眠3秒钟,继续循环，知道成功或重试次数结束  
 
    def select (self, sql = ''):  
        try:  
            self._reConn()  
            self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)  
            self.cursor.execute (sql)  
            result = self.cursor.fetchall()  
            self.cursor.close ()  
            return result  
        except MySQLdb.Error,e:  
            #print "Error %d: %s" % (e.args[0], e.args[1])  
            return False  
 
    def select_limit (self, sql ='',offset = 0, length = 20):  
        sql = '%s limit %d , %d ;' % (sql, offset, length)  
        return self.select(sql)  
 
    def query (self, sql = ''):  
        try:  
            self._reConn()  
            self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)  
            self.cursor.execute ("set names utf8") #utf8 字符集  
            result = self.cursor.execute (sql)  
            self.conn.commit()  
            self.cursor.close ()  
            return (True,result)  
        except MySQLdb.Error, e:  
            return False  
 
    def close (self):  
        self.conn.close()  
 
if __name__=='__main__':  
    my = mysql('localhost','root','password','database',3306)  
    print my.select_limit('select * from sdb_admin_roles',1,1)  
    #my.close()

 

五 31

MySQL的两种常用数据库存储引擎：MyISAM与InnoDB

Posted on by

问题1：MySQL的data里的网站数据库里只有.frm 而没有.MYD和.MYI文件？

解释：首先排除误删的情况，那么，你的此数据库的存储引擎可能为innoDB类型（把数据库导入为.sql后，可以在每个表结构后看到写着“ENGINE=InnoDB”；或者使用phpmyadmin查看数据表时，在“类型”列下，显示每个表的类型为“ENGINE=InnoDB”）。
那么，在windows主机下查看数据库目录./mysql/data/数据库名/ ，这样只能看到.frm文件和一个db.opt文件，它的数据文件位置在“./mysql/data/ibdata1”文件中保存着。

问题2：直接在服务器中直接迁移WordPress网站的数据库文件则数据丢失？

解释：WordPress博客系统是数据库引擎是InnoDB类型的，所以，看了第一个问题的解释后，这第二个问题就明白了。
那么，要直接对WordPress系统网站的数据文件迁移的时候，同时也要迁移这个文件（“./mysql/data/ibdata1”），网站就会正常显示出原数据了。

相关知识：

MyISAM与InnoDB是mysql目前比较常用的两个数据库存储引擎，MyISAM与InnoDB的主要的不同点在于性能和事务控制上。这里简单的介绍一下两者间的区别和转换方法：

MyISAM：MyISAM是MySQL5.5之前版本默认的数据库存储引擎。MYISAM提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用。但不支持事务、也不支持外键。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

InnoDB：InnoDB是MySQL5.5版本的默认数据库存储引擎，不过InnoDB已被Oracle收购，MySQL自行开发的新存储引擎Falcon将在MySQL6.0版本引进。InnoDB具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。尽管如此，但是InnoDB包括了对事务处理和外来键的支持，这两点都是MyISAM引擎所没有的。

又学习了！

mysql索引类型（normal、unique、full textl）及索引方法（BTree、Hash）的概念与使用场景

mysql索引方法

六 02

MySql获取INSERT后的自增ID、MySql设置变量

Posted on by

表结构：

0
1
2
3
4
5
6
7
8
9
10
CREATE TABLE `table_a` (
  `aid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
 
CREATE TABLE `table_b` (
  `mid` mediumint(8) NOT NULL,
  `name` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

table_a表中aid为自增长字段

要求使用在插入表table_a后的自增字段aid值做为表table_b的mid字段值，并且用这个值连接固定字符串“abc”后做为字段name2的值，完全使用MySql语言。

0
1
2
INSERT INTO `table_a` (aid,name) values ('','haha');
SELECT @len:=max(aid) FROM destoon_a;
INSERT INTO `table_b` (mid,name2) VALUES ('', CONCAT('abc', @num));

设置变量@len

max(aid) 获取mysql在插入一条数据后的自增ID值

CONCAT(‘abc’, @num) 按照参数的顺序拼接成一个字符串做为返回值

知识点：

mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。

第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量

第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……

注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”

 

三 24

DedeCMS常用SQL语句

Posted on by

在DedeCMS 系统中，我们很多地方需要用到SQL语句，例如批量修改（替换）内容、数据内容调用等，在系统模板中有一个专门用于调用数据的模板标签{dede:sql/}我们可以参考帮助中心的模板标签说明学习下这个标签如何使用。
当然我们在使用SQL语句和学习SQL语句的之前需要对织梦的数据库结构有一个大致的了解，我们可以通过帮助中心的数据库说明，来简单的了解这些内容。

以下为引用的内容：
标签名称：sql功能说明：用于从模板中用一个SQL查询获得其返回内容适用范围：全局使用基本语法：{dede:sql sql=”"}底层模板{/dede:sql}参数说明：sql=”" 完整的SQL查询语句底层模板字段：SQL语句中查出的所有字段都可以用[field:字段名/]来调用
应用实例：
1.调用某个特定会员发布的文章内容

0
1
2
{dede:sql sql='Select * from dede_archives where mid=1'}
<a href='/plus/view.php?aid=[field:id/]' target='_blank'>[field:title/]</a>
{/dede:sql}

mid为用户ID可以参考二次开发中关于dede_archives的数据表字段介绍

接下来我们收集整理一些常用的SQL语句帮助大家一同更好的使用DedeCMS系统。 Continue reading →

三 24

织梦常用SQL语句合集

Posted on by

织梦SQL语句文章点这里 织梦SQL常用命令语句（后台→系统→SQL命令行工具→运行SQL命令行：单行命令或多行命令），如下：

增：

  1. 增加一条记录：
    0
    INSERT INTO dede_payment values (7,'alipaywap','支付宝手机网站支付',0,'手机登陆时使用的支付方式',5,'a:0:{}',1,0,1)

    或
    0
    1
    2
    //可以写一行，也可以写成两行
    INSERT INTO dede_payment (id,code,name,fee,description,rank,config,enabled,cod,online)
    values (7,'alipaywap','支付宝手机网站支付',0,'手机登陆时使用的支付方式',5,'a:0:{}',1,0,1)

查：

  1. 查询后按要求返回正序或倒序记录列表：
    0
    1
    2
    3
    4
    //查询表dede_payment按id正序显示返回记录
    SELECT * FROM dede_payment ORDER by id;
     
    //倒序
    SELECT * FROM 表名 ORDER by id DESC;
  2. 查询后返回最后一条记录：
    0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    //mySQL可用 正序 语法：取某字段值进行排序，返回指定条数
    SELECT * FROM 表名 ORDER BY 字段名 LIMIT 返回记录条数;
     
    //mySQL可用 倒序 语法：按ID倒序排序后，返回条数为1时，即为取得此表的最后一条记录
    SELECT * FROM 表名 ORDER BY 字段名 DESC LIMIT 返回记录条数;
     
    //实例
    SELECT * FROM dede_payment ORDER BY id DESC LIMIT 1;
     
    //适用SQLServer/Oracle，在mySQL中不可用
    SELECT top 1 * FROM 表名 ORDER by id DESC;
  3. 查询表记录总数：
    0
    1
    SELECT count(*) FROM 表名;           //语法
    SELECT count(*) FROM dede_payment;  //实例
  4. 查询某条记录（查询并返回单条记录）：
    0
    1
    SELECT * FROM 表名 WHERE 查询条件;        //语法1
    SELECT * FROM dede_payment WHERE id=2;  //实例
  5. 查询某字段中值不相同的记录数：
    0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    //语法1 返回共有多少不同值的数量（总数）
    SELECT count(distinct(字段名)) FROM 表名;
     
    //实例1
    SELECT count(distinct(fee)) FROM dede_payment;
     
    //语法2 返回数组 每种不同值的记录数
    SELECT count(*) FROM 表名 GROUP BY 字段名;
     
    //实例2
    SELECT count(*) FROM dede_payment GROUP BY cod;

改：

  1. 将所有文档设置为“仅动态”：
    0
    UPDATE dede_archives SET ismake='-1'
  2. 将所有文档设置为“仅静态”：
    0
    UPDATE dede_archives SET ismake='1'
  3. 将所有栏目设置为“使用动态页”：
    0
    UPDATE dede_arctype SET isdefault='-1'
  4. 将所有栏目设置为“使用静态页”：
    0
    UPDATE dede_arctype SET isdefault='1'
  5. 修改所有文章命名规则：
    0
    UPDATE dede_arctype SET namerule='{typedir}/{aid}.html'
  6. 修改所有列表命名规则：
    0
    UPDATE dede_arctype SET namerule2='{typedir}/list-{tid}-{page}.html'
  7. 单条件筛选修改文章命名规则：
    0
    UPDATE dede_arctype SET namerule='{typedir}/{aid}.html' WHERE id='1'
  8. 多条件筛选修改文章命名规则：
    0
    UPDATE dede_arctype SET namerule='{typedir}/{aid}.html' WHERE id='7' AND reid='1'
  9. 将指定ID栏目下所有文档设置为“仅动态”：
    0
    UPDATE dede_archives SET ismake='-1' WHERE typeid='2'
  10. 将指定ID栏目下所有文档设置为“仅静态”：
    0
    UPDATE dede_archives SET ismake='1' WHERE typeid='2'
  11. 替换所有文档中body字段某字符串为其它字符串：
    0
    UPDATE dede_addonarticle SET body=replace (body,'被替换的关键词','替换后的关键词')

删：

  1. 删除一条记录（指定记录）：
    0
    DELETE FROM dede_payment WHERE id = 7;
  2. 删除多条记录（指定记录，可以为不连续记录）：
    0
    DELETE FROM dede_payment WHERE id in (7,8);
  3. 删除多条连续记录：
    0
    DELETE FROM dede_payment WHERE id >= 7 and id <= 9;

这些SQL语句已经够用了，只是示例，自己也可以在本地环境中做实验，具体的要结合到自己的网站，如表名、字段名、ID等。

注：1.数据库在使用SQL语句之前做好备份，防止有意外情况发生，无法恢复数据；2.在实际使用SQL语句过程中，要注意自己的表名和字段名，看是不是默认的表前缀和字段名。