- mysql还是建议绿色安装可惜我没有
- mysql字符集
- 调整字段顺序
- alter table JC_CONFIG modify office_home VARCHAR(255) after email_validate;
- 或者add时直接after
1.注意安装路径需要\
2.更改密码(密码字段：authentication_string)
3.密码过期
6.linux下安装
1.查询sql mode:
select @@sql_mode;
2.更改当前console的sql mode:SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
3.依照链接更改的是my.ini，更改影响范围为全局，需在bin目录下使用net stop mysql和net start mysql重启mysql
8、centos7安装mysql client
1、卸载mariadb-libs:rpm -e --nodeps mariadb-libs
2、依次安装mysql-community-common、mysql-community-libs、mysql-community-client
8、表分区
1、创建基于时间字段的表分区(最好不要使用pmax,不要使用range有bug,5.7可以使用range columns)
CREATE TABLE `ncov`.`es_bak` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '填报id',
`is_out` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '是否外出(1:是 0:否)',
`report_time` datetime(0) NOT NULL DEFAULT NOW() COMMENT '填报时间',
PRIMARY KEY (`id`,`report_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 88 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '疫情统计表' ROW_FORMAT = Dynamic
partition by range COLUMNS(report_time)(
PARTITION p20200218 VALUES LESS THAN ('20200218'),
partition pmax values less than (maxvalue)
);
2、更改表分区
ALTER TABLE es_bak REORGANIZE PARTITION pmax INTO (
PARTITION p20200220 VALUES LESS THAN ('20200220'),
PARTITION pmax VALUES LESS THAN (maxvalue)
);
3、更改普通表为分区表
ALTER TABLE es_bak partition by range COLUMNS(report_time)(
PARTITION p20200218 VALUES LESS THAN ('20200218'),
partition pmax values less than (maxvalue)
);
4、查看表分区内数据条数
SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'epidemic_statistics'
5、每日新增当天表分区
drop procedure if exists add_date_range_part;
CREATE PROCEDURE add_date_range_part(In table_name varchar(30), in date varchar(10))
BEGIN
DECLARE p_name VARCHAR(9);
-- partition名赋值
-- set p_name = (select concat('p',date_format(date_sub(curdate(),interval - 1 day),'%Y%m%d'))from dual);
set p_name = (select concat('p',date)from dual);
-- 添加分区sql语句
set @p_sql = concat('ALTER TABLE ',table_name,' ADD PARTITION(PARTITION ',p_name,' VALUES LESS THAN (',date,'))');
-- 预处理sql，其中stmt是一个变量
PREPARE stmt FROM @p_sql;
-- 执行SQL语句
EXECUTE stmt;
-- 释放掉预处理段
DEALLOCATE PREPARE stmt;
END
6、添加分区
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
7、批量导出表结构
SELECT
TB.TABLE_SCHEMA, -- 模式
TB.TABLE_NAME, -- 表名
TB.TABLE_COMMENT, -- 表名注释
COL.COLUMN_NAME, -- 字段名
COL.COLUMN_TYPE, -- 字段类型
COL.COLUMN_COMMENT -- 字段注释
FROM
INFORMATION_SCHEMA.TABLES TB,
INFORMATION_SCHEMA.COLUMNS COL
Where TB.TABLE_SCHEMA = 'exam' -- 数据库名
AND TB.TABLE_ROWS >0 --非空表
AND TB.TABLE_NAME = COL.TABLE_NAME
8、mysql查询条件为字符串等号后常量需加引号，如是数字，会查询出该数字开头的所有记录，因mysql会把字符串转为数字，转不成的直接舍去了
10、批量备份表
drop procedure if exists copy_table_by_addtime;
create procedure copy_table_by_addtime(in source_table varchar(100), in target_table varchar(100), in start_time int(11), in end_time int(11), in page_size int(11))
begin
declare total_count int(11);
declare start_num int(11);
SET autocommit=0;
-- 起始行数
set start_num = 0;
-- 待拷贝数据总条数
set @p_sql = concat('select count(1) into @totalCount from ',source_table,' where addtime >= ',start_time,' and addtime < ',end_time);
prepare stmt from @p_sql;
execute stmt;
deallocate prepare stmt;
set total_count = @totalCount;
while start_num < total_count do
-- 分段导入
set @p_sql = concat('insert into ',target_table,' select * from ',source_table,' where addtime >= ',start_time,' and addtime < ',end_time,' order by addtime limit ',start_num,' , ',page_size);
-- 预处理sql，其中stmt是一个变量
prepare stmt from @p_sql;
-- 执行sql语句
execute stmt;
-- 释放掉预处理段
deallocate prepare stmt;
commit;
set start_num = start_num + page_size;
end while;
end；
mysqldump -uUSERNAME -pPASSWORD --skip-opt -q -t -e -hHOST -P3306 "--where=addtime is not null" DATABASE TABLE> TABLE.sql
create procedure delete_table_by_key(in target_table varchar(100), in pri_key varchar(100), in max_key int(11),in page_size int(11))
begin
declare start_num int(11);
SET autocommit=0;
-- 起始行数
set start_num = 0;
while start_num <= max_key do
-- 分段导入
set @p_sql = concat('delete from ',target_table,' where ',pri_key, ' <= ', max_key, ' limit ',page_size);
-- 预处理sql，其中stmt是一个变量
prepare stmt from @p_sql;
-- 执行sql语句
execute stmt;
-- 释放掉预处理段
deallocate prepare stmt;
commit;
set start_num = start_num + page_size;
select sleep(1);
end while;
end;
#shell
sql="call copy_table_by_addtime('$1', '$2', $3, $4, $5)"
echo $sql
echo 'start_time:'`date +%Y-%m-%d,%H:%m:%s`
/data/software/mysql-5.7.26-el7-x86_64/bin/mysql -hXX-P3306 -uXX -pXX -DXX -e"${sql}"
echo 'end_time:'`date +%Y-%m-%d,%H:%m:%s`
11、
left join on XX = XX and YY = YY写法错误应改写为left join on XX = XX where YY = YY
12、添加range columns partitions存储过程
create procedure auto_set_range_columns_partitions (in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, in partition_number int, in partitiontype int, in gaps int)
L_END:
begin
-- databasename：创建分区的数据库
-- tablename：创建分区的表的名称
-- partition_number：一次创建多少个分区
-- partitiontype：分区类型[0按天分区，1按月分区，2按年分区
-- gaps：分区间隔
declare max_partition_description varchar(255) default '';
declare p_name varchar(255) default 0;
declare p_description varchar(255) default 0;
declare isexist_partition varchar(255) default 0;
declare i int default 0;
-- 查看对应数据库对应表是否已经有手动分区[自动分区前提是必须有手动分区]
select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename and table_name = tablename limit 1;
-- 如果不存在则打印错误并退出存储过程
if isexist_partition <=> "" then
select "partition table not is exist" as "ERROR";
leave L_END;
end if;
-- 获取最大[降序获取]的分区描述[值]
select partition_description into max_partition_description from information_schema.partitions where table_schema = databasename and table_name = tablename order by partition_description desc limit 1;
-- 如果最大分区没有,说明没有手动分区,则无法创建自动分区
if max_partition_description <=> "" then
select "partition table is error" as "ERROR";
leave L_END;
end if;
-- 替换前后的单引号[''两个引号表示一个单引号的转义]
-- set max_partition_description = REPLACE(max_partition_description, '''', '');
-- 或使用如下语句
set max_partition_description = REPLACE(max_partition_description, '\'', '');
-- 自动创建number个分区
while (i < partition_number) do
if (partitiontype = 0) then
-- 每个分区按天递增,递增gaps天
set p_description = DATE_ADD(max_partition_description, interval i*gaps day);
elseif (partitiontype = 1) then
-- 每个分区按月递增,递增gaps月
set p_description = DATE_ADD(max_partition_description, interval i*gaps month);
else
-- 每个分区按年递增,递增gaps年
set p_description = DATE_ADD(max_partition_description, interval i*gaps year);
end if;
-- 如果有横杆替换为空
set p_name = REPLACE(p_description, '-', '');
-- 例如10.20的记录实际是less than 10.21
set p_description = DATE_ADD(p_description, interval 1 day);
set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (\'', p_description ,'\'))');
-- 准备sql语句
PREPARE stmt from @sql;
-- 执行sql语句
EXECUTE stmt;
-- 释放资源
DEALLOCATE PREPARE stmt;
-- 递增变量
set i = (i + 1) ;
end while;
end;
--查询表分区数据量
select
partition_name part,partition_expression expr,partition_description descr,
from_unixtime(partition_description,'%Y-%m-%d') expirydate,table_rows
from
information_schema.`partitions` where table_name = 'XX';
