随笔记

平凡人平凡路，沉下心迈出步

0%

Mysql常用指令记录

Posted on Edited on In Valine:
MySQL常用的一些语句记录，想不起来的时候来这里看看

创建Scheme

1
2
3

DROP DATABASE IF EXISTS `db_scheme`;
create database `db_scheme` default character set utf8mb4 collate utf8mb4_general_ci;

数据导入命令

1
2

mysql -uroot -p123456 -Ddb_log < ./db_log.sql

导出数据

1
2
3
4
5
6

mysqldump --extended-insert=FALSE -u root -p db_poker tb_club > dump.sql   导出insert为多行`--extended-insert=FALSE`

多行insert

--skip-extended-insert

DDL语句

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
drop database  IF EXISTS AiMacauAnalyse;

create database AiMacauAnalyse DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use AiMacauAnalyse;


DROP TABLE IF EXISTS `t_analyse_gametotal`;
CREATE TABLE `t_analyse_gametotal` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dbkey` bigint(20) DEFAULT '0',
  `ukey` varchar(50) DEFAULT NULL,
  `client_type` int(20) DEFAULT '0',
  `cp_id` varchar(20) DEFAULT NULL,
  `dnu` int(11) DEFAULT '0',
  `dau` int(11) DEFAULT '0',
  `wau` int(11) DEFAULT '0',
  `mau` int(11) DEFAULT '0',
  `login_times` int(11) DEFAULT '0',
  `time_length` int(11) DEFAULT '0',
  `new_pay_num` int(11) DEFAULT '0',
  `pay_money` double(20,2) DEFAULT '0.00',
  `pay_num` int(11) DEFAULT '0',
  `sec_num` int(11) DEFAULT '0',
  `three_num` int(11) DEFAULT '0',
  `seven_num` int(11) DEFAULT '0',
  `mpay` double(20,2) DEFAULT '0.00',
  `mpay_num` int(11) DEFAULT '0',
  `pay_times` int(11) DEFAULT '0',
  `mpay_times` int(11) DEFAULT '0',
  `create_time` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ukey` (`ukey`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS db_poker_sgame.`tb_player_daily_profit`;
CREATE TABLE `tb_player_daily_profit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `nickName` varchar(20) DEFAULT '',
  `userName` varchar(20) NOT NULL DEFAULT '',
  `clubId` int(11) NOT NULL,
  `clubName` varchar(20) NOT NULL DEFAULT '',
  `superClubId` int(11) NOT NULL,
  `superClubName` varchar(20) NOT NULL DEFAULT '',
  `sumPlayerProfit` bigint(20) NOT NULL DEFAULT '0' COMMENT '玩家总盈亏',
  `sumValidBet` bigint(20) NOT NULL DEFAULT '0' COMMENT '有效下注',
  `sgameRebateNegative` float(4,4) NOT NULL DEFAULT '0.00' COMMENT '俱乐部街机游戏分成比例-负数',
  `sgameRebatePostive` float(4,4) NOT NULL DEFAULT '0.00' COMMENT '俱乐部街机游戏分成比例-正数',
  `sgameBetRebate` float(4,4) NOT NULL DEFAULT '0.00' COMMENT '街机游戏投注分成比例',
  `sumClubProfit` bigint(20) NOT NULL DEFAULT '0' COMMENT '俱乐部总盈亏',
  `sumPlatformProfit` bigint(20) NOT NULL DEFAULT '0' COMMENT '平台总盈亏',
  `perDay` varchar(11) NOT NULL  COMMENT '单日',
  `countEndTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_UNIQUE` (`pid`,`perDay`),
  index `IDX_CLUB_ID`(`clubId`),
  index `IDX_DAY`(`perDay`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

清理语句

1
2
3
4

DELETE from t_analyse_gametotal where dbkey <20171001;
Truncate `t_coin_analyse_user_day_slots201709`;
DROP TABLE IF EXISTS `t_coin_user_action201708`;

索引添加

1
2
3
4
5
6
ALTER TABLE `t_analyse_user_day_slots` ADD INDEX `uid` (`uid`) USING BTREE ;

-- 增加主键
ALTER TABLE tb_statistics ADD CONSTRAINT tb_statistics_pk UNIQUE (pid, ptype, pday, pclubId,recordTime);
-- 删除主键
DROP INDEX tb_statistics_pk ON tb_statistics;

触发器

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
DROP TRIGGER t_trigger_cpid;

DELIMITER $
CREATE TRIGGER `t_trigger_cpid` AFTER insert ON `t_user_status_info`
FOR EACH ROW 
BEGIN
   DECLARE v_num int;
   DECLARE v_dt int;
   DECLARE v_ukey VARCHAR(60);
   set @dt = NOW();
SELECT DATE_FORMAT(@dt, '%Y%m%d') INTO v_dt;
SELECT 
    num
INTO v_num FROM
    t_analyse_cp
WHERE
    cp_id = new.cp_id;
SELECT CONCAT(v_dt, '_', new.cp_id) INTO v_ukey;
   if new.reg_time = v_dt then 
    if v_num >0 then
     update t_analyse_cp set num=v_num+1 where ukey=v_ukey;
    else
     insert into t_analyse_cp(dbkey,cp_id,num,ukey) values(v_dt,new.cp_id,1,v_ukey);
    end if;
  end if;
end$
DELIMITER ;

导入数据

1
mysql slotGame < AiMacau***.sql

修改数据库密码

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
方法1： 用SET PASSWORD命令

　　mysql -u root

　　mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

方法2：用mysqladmin

　　mysqladmin -u root password "newpass"

　　如果root已经设置过密码，采用如下方法

　　mysqladmin -u root password oldpass "newpass"

方法3： 用UPDATE直接编辑user表

　　mysql -u root

　　mysql> use mysql;

　　mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';

　　mysql> FLUSH PRIVILEGES;

在丢失root密码的时候，可以这样

　　mysqld_safe --skip-grant-tables&

　　mysql -u root mysql

　　mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';

　　mysql> FLUSH PRIVILEGES;

mysql cnf配置

1
2
3
4
5
6
7
8
# Default Homebrew MySQL server config
# mac 配置路径/usr/local/etc/my.cnf
[mysqld]
# Only allow connections from localhost
max_connections = 5000
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
wait_timeout=1814400
default-time_zone = '+8:00'

查询MySQL系统参数

1
2
3
4
5
show variables like 'max_connections';
show variables like '%connections%';
show global variables like '%connection%';

show status like '%connections%';

查看表空间占用

1
2
3
4
5
6
7
8
9

select
table_schema,
table_name,
table_rows,
truncate(data_length/1024/1024, 2) as 'MB',
truncate(index_length/1024/1024, 2) as 'MB'
from information_schema.tables
order by data_length desc, index_length desc;

mysql sum 函数 处理null as zero问题

1
COALESCE(SUM(actionCount),0)

mysql 使用存储过程插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12

DROP PROCEDURE IF EXISTS proc_initData; -- 如果存在此存储过程则删掉
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=10000 DO
        insert into tb_reportagroupofcardlog value (0,i,i,0,1,1,'[]','','',0,'');
        SET i = i+1;
    END WHILE;
END $
CALL proc_initData();

mysql 允许ip访问

1
2
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

explain 分析

1
2
3
4
5
6
7
8
9
explain SELECT * FROM db_poker_video.tb_video_0 WHERE `roomType`=3
        AND handEndTime >=1558800000000
        AND handEndTime <= 1564131900005
        ORDER BY handEndTime DESC LIMIT 0,25
        

| id | select_type | partitions | type  | possible_keys                | key                          | key_len | ref    | rows | filtered | Extra                              |
|----|-------------|------------|-------|------------------------------|------------------------------|---------|--------|------|----------|------------------------------------|
| 1  | SIMPLE      | <null>     | range | tb_video_0_handEndTime_index | tb_video_0_handEndTime_index | 8       | <null> | 5959 | 10       | Using index condition; Using where |