mysql超级无敌详细的经验

时间字符换比较

SELECT count(1) FROM article where STR_TO_DATE(create_time,'%Y-%m-%d %H:%i:%s') > STR_TO_DATE('2019-10-17 08:00:40','%Y-%m-%d %H:%i:%s')

查询出重复的记录

select channel_code,count(*) as count from channel where client_license_id ='hnr' group by channel_code having count>1 ;

1.对应数字:

长度是用来告诉你该类型最多显示多少位的，类似于注释，并不起任何作用。如：tinyint(4), tinyint(80), tinyint(0) 三者没有任何区别，该类型最多还是只能存储1字节大小（即-127-128）；

2.对应字符串:

varchar(20)表示最大20个字符（无论中文还是字母，只算字符个数，不论字节）。

如果导入的表 过大

 
 show VARIABLES like '%max_allowed_packet%';
 set global max_allowed_packet = 1024*1024*1024;

SQL 中 【不等于】使用‘<>’，此时要注意此条件会将字段为null的数据也当做满足不等于的条件而将数据筛选掉。




要查出含有‘null’只需将SQL 改为 ：select * from A where B1<>1 or B1 is null 即可。

因此对NULL进行判断处理时，只能采用IS NULL或IS NOT NULL，而不能采用=, <, <>, !=这些操作符。

所以在mysql中尽量 都给上默认值 int 0 string emty string

另外 0表示不 1表示肯定

重点关注这个 key就是他实际用到所以 还有rows 他查了多少条    

还有type 一般最好是ref

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

组合索引 当你有两个查询条件  比如 a =1 b =2 本来如果只有 a是索引 那么 本来需要查询2行数据 如果 加上b索引就可以看到 row等于1 

但是尤其需要注意的是 这个组合索引 在前后顺序问题 （a，b） 这 where a=? 只有一个查询条件 走索引 反之 where b=? 就不走索引

SELECT
    COLUMN_NAME as 字段名称,
    COLUMN_TYPE as 数据类型,
    if(COLUMN_KEY='PRI','Y','N') as 主键,
    COLUMN_COMMENT as 备注
FROM
    INFORMATION_SCHEMA. COLUMNS
WHERE
    table_schema = 'saas'
AND 
    table_name = 'article'

查询表大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES 
where  table_name='article';

Data truncation: Incorrect string value: '\xF0\x9F\x91\x87\xE8\xBF...' 升级mysql

<version>8.0.25</version>

导数据：show VARIABLES LIKE  'max_binlog_cache_size'
set global max_binlog_cache_size=18446744073709547520

<version>8.0.25</version>

/*展示binlog文件*/
show binary logs

/*展示binlog文件 STATEMENT 显示sql ROW不显示 sql*/
show GLOBAL VARIABLES like '%binlog_format%'

set SESSION binlog_format = 'STATEMENT'

show binlog events in 'binlog.000003';
/*在这个会话里变量使不生效的需要退出后在进入查看mysql变量就生效，修改好后记得要修改my.cnf文件让mysql在重启后变量值不变，要注意的是使用set global设置mysql的变量值需要在配置的时候*/
/*显示慢查询日志*/
SHOW VARIABLES LIKE 'slow_query%';
/*设置慢查询时间*/
SHOW VARIABLES LIKE 'long_query_time'

SET GLOBAL long_query_time=0.001
/*设置慢查询开关*/
set GLOBAL slow_query_log = 'ON'

/*所有日志*/
 show variables like 'general_log';
 
  show variables like 'general_log_file';
 
 
  SET GLOBAL general_log=on;

metadata lock是表级锁，是在server层加的，适用于所有存储引擎。所有的dml 和DQL (查询)操作都会在表上加一个metadata读锁；所有的ddl操作都会在表上加一个metadata写锁。读锁和写锁的阻塞关系如下：

1

读锁和写锁之间相互阻塞，即同一个表上的dml和ddl之间互相阻塞。

2

写锁和写锁之间互相阻塞，即两个session不能对表同时做表定义变更，需要串行操作。

3

读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞，可以并发执行，日常工作中大家看到的dml之间的锁等待是innodb行锁引起的，和metadata lock无关。

其实就是一句话 只要 执行DDL 那么会阻塞 整张表

可以看到表锁

select * from performance_schema.metadata_locks

查询当前mysql版本号

select version();

//表锁等待时间 默认1年
show variables like 'lock_wait_timeout'

//行锁等待时间 默认50s
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

但是按理说DDL操作会加写锁 但是实际情况并不是，ddl操作大多可以online执行，因此即使有写锁，也会很快降级为读锁

什么叫做online 呢

• Copy Table方式： 这是InnoDB最早支持的方式。顾名思义，通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临时表，然后Rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。
• Inplace方式：这是原生MySQL 5.5，以及innodb_plugin中提供的方式。所谓Inplace，也就是在原表上直接进行，不会拷贝临时表。相对于Copy Table方式，这比较高效率。原表同样可读的，但是不可写。

• Online方式：这是MySQL 5.6以上版本中提供的方式，也是今天我们重点说明的方式。无论是Copy Table方式，还是Inplace方式，原表只能允许读取，不可写。对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式DDL。与以上两种方式相比，online方式支持DDL时不仅可以读，还可以写，对于dba来说，这是一个非常棒的改进 但是还是会有一段时间不能写

1. 拿MDL写锁

2. 降级成MDL读锁，接受读写操作，读操作直接正常返回，写操作会被记录下来，等待后面更新到临时表中。

3. 真正做DDL，在 DDL 表记录向临时表转移完成并执行完记录的写操作后开始阻塞读写操作。

4. 升级成MDL写锁，进行临时表的替换

5. 释放MDL锁

详谈 MySQL Online DDL - 茁壮的小草 - 博客园

update article set streams = REPLACE(streams,'"liveId"',CONCAT('"backUrl:"',back_url,'","liveId"'))where article_id = 1415961155431104513 

可重复读 

就是 必须是事务开启时 第一次 读取到的数据 保持不变 一直到事务结束 时 也就是说 不过在这个事务进行的过程中 其他事务是否提交了数据 都不管 只要在这个事务里  无论读取的数据有多少次

都是和第一次读取的一样

mysql 行锁 联合索引 会升级为表锁 此处的表锁 才是真正的表锁 上面那个表锁 又叫做 元数据表锁 主要 是变更变结构 用 

行锁 只有那个查询条件有索引的时候 才会用 如果是联合索引 那还是表锁  如果 更新的数据量过大 由数据库自行判断 用explain看一下 看用到锁了么  

一旦成为表锁那么 整张表 都不能更新  都是还是能查询的 因为select 不加锁

一旦成为行锁 那么只有那几条记录不能更新

ALTER TABLE article MODIFY COLUMN cover_video_url VARCHAR(1000), ALGORITHM=INPLACE; 不用默认的copy表

MySQL InnoDB 修改表列Online DDL_孙瑞宇的博客-CSDN博客

m1 ::: SELECT a.*
         
            ,b.content
         
               from article a
         
            LEFT JOIN article_content b on a.article_content_id = b.article_content_id
         
          WHERE a.tenant_id = ?
         
        and a.article_id
        IN
         (  
            ?
         ) ::: [1, 1572158431411912705]

记录一次content字段映射不上的问题  是因为 在分库查询时 这样的联表查询 是表模板是以 0库的article字段为准（相当于key值）  这个时候我新加了一个字段 但是为了省事只在1库加了

 

导致最后校验字段的数量的时候 刚好把最后的content给省略掉了 但是如果把content提前 省略的就是最后一个字段   所有本次经验就是分库表 要加字段 全加上 尤其是0库默认库

需求是这样的 统计一个张表所有视频时长》1800s的数据

但是表里的数据是json

[{"type":"10","fieldValue":"1800","optionValue":"null","label":"视频时长"},{"type":"4","fieldValue":"true","optionValue":"[]","label":"是否显示标题","required":false}]

SELECT * from (SELECT article_id,substring_index(ext_fields,'{"type":"10","fieldValue":"',-1)  as ext_fields from article where article_type = 1 and article_status = 11 and app_id != 1427918398613422082 and article_id=1719185653608108034 )A where A.ext_fields > 1799

可以这样写 这样截取的字符串可能是9060","label":"视频时长"}。。。。。只要最前面是数字就可以参与比较