Mysql 008基础操作

 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

CREATE TABLE r_miner_netbusi_d_range (
    ftime          bigint DEFAULT NULL COMMENT '日期',
    busi_name      int    DEFAULT NULL COMMENT '业务类型:-1总计',
    nettype        int    DEFAULT NULL COMMENT '网络类型',
    devicenum      bigint DEFAULT NULL COMMENT '矿机数量',
    maxuse_bandwidth  float DEFAULT NULL COMMENT '带宽峰值',
    userset_bandwidth float DEFAULT NULL COMMENT '用户总带宽',
    created timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) 
ENGINE=InnoDB 
DEFAULT CHARSET=utf8 
COMMENT='矿机业务网络类型表'
PARTITION BY RANGE (ftime)(
    PARTITION p20160401 VALUES LESS THAN (20160402) ,
    PARTITION p20160402 VALUES LESS THAN (20160403) ,
    PARTITION p20160404 VALUES LESS THAN (20160405) ,
    PARTITION pMax VALUES LESS THAN MAXVALUE 
);

-- 表重命名:  
alter table r_miner_netbusi_d rename r_miner_netbusi_d_20160406;
-- 字符串转日期
str_to_date('2016-04-06 02:04:09','%Y-%m-%d %H:%i:%s') 
-- 增加字段：
alter table r_miner_netbusi_d add column pcn varchar(100) comment '分组标示' after nettype;

-- 增加分区
ALTER TABLE r_miner_netbusi_d_range ADD PARTITION (
    PARTITION p20160301 VALUES LESS THAN (20160302)
);
-- 重新分区
ALTER TABLE r_miner_netbusi_d_range reorganize partition pMax into (  
    partition p20160331 values less than (20160401),  
    partition p20160403 values less than (20160404),  
    partition p20160404 values less than (20160405),
    partition pMax values less than maxvalue 
); 
ALTER TABLE r_miner_netbusi_d_range reorganize partition p20160404 into (  
    partition p20160403 values less than (20160404),  
    partition p20160404 values less than (20160405) 
); 

-- 子分区其实是对每个分区表的每个分区进行再次分隔，
-- 目前只有RANGE和LIST分区的表可以再进行子分区，子分区只能是HASH或者KEY分区。
-- 子分区可以将原本的数据进行再次的分区划分

alter table test.tb1 reorganize partition pmax into (
  PARTITION p20221101 VALUES LESS THAN ('2022-12-01 00:00:00'),
  PARTITION p20221201 VALUES LESS THAN ('2023-01-01 00:00:00'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- 获取批量被锁的线程
select concat('KILL ',id,';') 
  from information_schema.processlist 
 where user=’root’;

select * from information_schema.innodb_trx;

-- mysql 授权 
grant all privileges on test.* to 'test'@'10.33.19.41' identified by '123456';

-- mysql回收权限 
revoke drop on test.* from test@'10.33.19.41';
flush privileges;

 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

-- 修改数据库字符集
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

-- 把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]

--如：
ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

-- 只是修改表的默认字符集：
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
-- 如：
ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

-- 修改字段的字符集：
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
-- 如：
ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci; 

-- 查看数据库编码：
SHOW CREATE DATABASE db_name; 
-- 查看表编码：
SHOW CREATE TABLE tbl_name; 
-- 查看字段编码：
SHOW FULL COLUMNS FROM tbl_name;

-- 设置mysql编码
set character_set_database=gbk;
set character_set_server=gbk;
set character_set_connection=gbk;
set character_set_client=gbk;
set character_set_results=gbk;

1
2

-- 显示数据库详细编码
show variables like '%char%'; 

1

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;  

1

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  

1

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  

1
2
3
4
5
6
7

# my.cnf
default-character-set = utf8mb4 
default-character-set = utf8mb4    
character-set-client-handshake = FALSE  
character-set-server = utf8mb4  
collation-server = utf8mb4_unicode_ci  
init_connect='SET NAMES utf8mb4'

1

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';  

1
2
3

set password for ambari@'%' = password('123456');
set password for ambari_hive@'%' = password('123456');
flush privileges;