MYSQL

1. mysql还是建议绿色安装可惜我没有
2. mysql字符集
3. 调整字段顺序

1. alter table JC_CONFIG modify office_home VARCHAR(255) after email_validate;
2. 或者add时直接after

4. mysql简易安装使用指南

---

5.mysql绿色安装

1.注意安装路径需要\
2.更改密码(密码字段：authentication_string)
3.密码过期

6.linux下安装

1.参考1
2.参考2
3.字符集

7、MySQL 5.7 ERROR 1055 解决办法

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_NAME = COL.TABLE_NAME

8、mysql查询条件为字符串等号后常量需加引号，如是数字，会查询出该数字开头的所有记录，因mysql会把字符串转为数字，转不成的直接舍去了

9、dbeaver连接redis