分类目录归档：Database

mysql通过存储过程以及事务批量生成数据

存储过程加上事务能够提高插入效率：

CREATE DEFINER=`root`@`%` PROCEDURE `autoinsert`(IN NUM INT)
BEGIN
  DECLARE INIT_NUM INT DEFAULT 0 ;
    START TRANSACTION;
    WHILE(INIT_NUM < NUM)
    DO
        insert into `user`(`name`,`city_id`) values(rand_str(10),rand_int(2));
        SET INIT_NUM = INIT_NUM+1;
    END WHILE;
    COMMIT;
    END


CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `forum_id` bigint(20) unsigned NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `test_forum_id_IDX` (`forum_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=750136 DEFAULT CHARSET=utf8mb4

DROP PROCEDURE IF EXISTS test.BatchInsertTest;
delimiter //
CREATE PROCEDURE BatchInsertTest(IN loop_time INT)
  BEGIN
      DECLARE Var INT;
      SET Var = 0;
      START TRANSACTION;
      WHILE Var < loop_time DO
          INSERT INTO `test` ( `forum_id`, `created`) select  FLOOR(1 + (RAND() * 1000)),FROM_UNIXTIME(UNIX_TIMESTAMP()-(RAND() * 30 * 86400));
          SET Var = Var + 1;
      END WHILE;
     COMMIT;
  END;
  //
delimiter ;

CALL BatchInsertTest(10000);

select count(*) from test;

mysql生成固定位数随机字母以及数字

mysql生成固定位数随机字母以及数字

随机数字

CREATE DEFINER=`root`@`%` FUNCTION `rand_int`(counts INTEGER) RETURNS varchar(20) CHARSET utf8
BEGIN
       DECLARE sTemp VARCHAR(20);
    DECLARE sTempCounts INTEGER;
       SET sTemp = CONCAT( ROUND(ROUND(RAND(),counts)*(POW(10,counts))),'');

    IF(CHAR_LENGTH(sTemp)<counts) THEN

      SET sTempCounts = counts - CHAR_LENGTH(sTemp);
      SET sTemp = CONCAT(sTemp, RIGHT(CONCAT(POW(10,sTempCounts),''),sTempCounts));
    END IF;
      RETURN sTemp;
END

随机字母：

CREATE DEFINER=`root`@`%` FUNCTION `rand_str`(  
    f_num INT UNSIGNED
    ) RETURNS varchar(200) CHARSET latin1
BEGIN
      DECLARE i INT UNSIGNED DEFAULT 0;   
      DECLARE v_result VARCHAR(200) DEFAULT '';   
      DECLARE v_dict VARCHAR(200) DEFAULT '';  
      SET v_dict = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';  
      SET v_dict = LPAD(v_dict,200,v_dict);  
      WHILE i < f_num   
      DO   
        SET v_result = CONCAT(v_result,SUBSTR(v_dict,CEIL(RAND()*200),1));  
        SET i = i + 1;   
      END WHILE;    
      RETURN v_result;   
END

mysql切换主库记录

原来公司采用的是异地Mysql机房。三台机器是A(主)->B(从，主)->C(从)模式。

今天因为测试磁盘性能不小心将B磁盘数据弄没了。(直接将这篇文章的sdb1换成sda2然后整个磁盘数据没有了)

于是要将C的主库从B切换到A上。

在C上找到最后同步的binlog

show slave status

在C上找到binlog的最后同步点

sudo mysqlbinlog --base64-output=decode-rows -v --set-charset=utf8  /home/mysql/mysql-bin.000015 --start-datetime="2016-12-19 11:38:00" --stop-datetime="2016-12-19 11:39:00"

在A上找到对应的同步点

mysqlbinlog  --base64-output=decode-rows -v --set-charset=utf8 /data/mysql/mysql-bin.001232 --start-datetime="2016-12-19 11:38:00" --stop-datetime="2016-12-19 11:38:50"

开始同步

change master to master_host=’x.x.x.x’, master_user=’slave’, master_password=’********’, master_log_file=’mysql-bin.001232′, master_log_pos=648129189;

同步发现没有权限，增加同步权限

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'x.x.x.x' IDENTIFIED BY 'password';

解决同步时出现Cannot execute the current event group in the parallel mode错误

set global slave_parallel_workers=0;

php mysql实现非连续不重复ID

非连续ID有很多用处.最大的一个用处就是作为订单id,用户id之类的不想让别人遍历(直接通过请求里面传id值来爬你的数据),以及不想让别人看出来你有多少数据量(如果是连续id,应用有多少订单,一目了然)的id.

mysql的实现方法

添加一个触发器,每次插入的时候通过取当前最大的id然后加一个随机数得到新的id.

   CREATE TRIGGER `rand_increase` BEFORE INSERT ON `users` FOR EACH ROW SET NEW.id=(SELECT max(id) FROM users)+FLOOR(RAND()*500)

php的实现方法

生成一个ID,到数据库里面查看ID是否存在,如果存在,再生成一个.

class model
{
    public function generateRandomId($length=15) {
        $random = "";
        for ($i = 0; $i < $length; $i++) {
            $random .= mt_rand(0, 9);
        }
        return $random;
    }
    public function beforeInsert() {
        do{
            $inserId=$this->generateRandomId(10);
        }while(!$this->selectById($inserId));
        $this->insertId=$inserId;
    }
}

理论上,在超高并发条件下,这两种方法都不是完美的解决办法,但是实际上只要你的id不是太短(比如15位以上?),不完美发生的概率非常非常非常非常非常非常非常非常小.

来源:php mysql实现非连续不重复ID

mysql随机字符串 

drop function if exists rand_string;  
create function rand_string(str_length tinyint unsigned, str_type tinyint unsigned) returns varchar(255)
begin
    -- Function   : rand_string
    -- Author     : reymondtu#opencfg.com
    -- Date       : 2011/03/27
    -- Params     : str_length int unsigned 
    --                  The random string length of random string
    --              str_type   int unsigned
    --                  The random string type
    --                      1.0-9
    --                      2.a-z
    --                      3.A-Z
    --                      4.a-zA-Z
    --                      5.0-9a-zA-Z
    --
    -- Example    :
    --
    -- mysql> select rand_string(32,5) from dual;
    -- +----------------------------------+
    -- | rand_string(32,5)                |
    -- +----------------------------------+
    -- | HbPBz4DWSAiJNLt4SgExHVwQI34bI6mt |
    -- +----------------------------------+
    -- 1 row in set

    declare counter int unsigned default 0;
    declare const_chars varchar(64) default '0123456789';
    declare result varchar(255) default '';
 
    if str_type = 1 then
        set const_chars = '0123456789';
    elseif str_type = 2 then
        set const_chars = 'abcdefghijklmnopqrstuvwxyz';
    elseif str_type = 3 then
        set const_chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    elseif str_type = 4 then
        set const_chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    elseif str_type = 5 then
        set const_chars = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    else
        set const_chars = '0123456789';
    end if;
 
    while counter < str_length do  
        set result = concat(result,substr(const_chars,ceil(rand()*(length(const_chars)-1)),1));
    set counter = counter + 1;
    end while;

    return result;
end

centos7基本系统安装(php-fpm,mariadb,nginx,epel)

安装epel

su -c 'rpm -Uvh http://mirrors.hust.edu.cn/epel/beta/7/x86_64/epel-release-7-0.2.noarch.rpm'
su -c 'yum install epel-release-7-0.2'

安装mariadb,php,nginx

su -c 'yum install mariadb mariadb-server php php-cli php-fpm php-pdo nginx'

创建数据库账户

CREATE USER 'th'@'%' IDENTIFIED BY '***';
GRANT ALL PRIVILEGES ON * . * TO 'th'@'%' IDENTIFIED BY '***';
flush privileges;