MySQL 高频脚本备忘录，以及日常经验备忘。

在终端连接 MySQL

假设 MySQL 的密码是：#mysqlPassword!#，主机 IP 是：192.168.1.230，用户名是：root。在 win10 的 cmd 窗口下，用以下脚本可直接连接。

1
mysql -h192.168.1.230 -uroot -p#mysqlPassword!#

把用户名用单引号引起来，密码不引，也可以直接连接。

1
mysql -h192.168.1.230 -u'root' -p#mysqlPassword!#

但是，如果把密码用单引号引起来，则会提示：

1
ERROR 1045 (28000): Access denied for user 'root'@'192.168.1.89' (using password: YES)

没有权限时（换一个 IP：192.168.1.29 连接测试），会提示：

1
ERROR 1130 (HY000): Host '192.168.1.29' is not allowed to connect to this MySQL server

SecureCRT 用以下脚本可直接连接，密码前后的单引号是必须的，否则会不识别密码，而用户名则可以引起来也可以不引。

1
mysql -u'root' -p'#mysqlPassword!#'

结论

在终端连接 MySQL时，关于单引号的使用，win10 与 SecureCRT 是有区别的。

  1. 密码，win10 下不能用单引号引起来，而 SecureCRT 则必须要用单引号引起来。
  2. 主机 IP 地址，在 win10 下，不能用单引号引起来，否则报以下错误，而 SecureCRT 则可用单引号引起来，也可不用。
    1
    2
    3
    C:\Users\AndyChen>mysql -h'192.168.1.230' -uroot -p#mysqlPassword!#
    mysql: [Warning] Using a password on the command line interface can be insecure.
    ERROR 2005 (HY000): Unknown MySQL server host ''192.168.1.230'' (2)

sql_safe_updates

用 MySQL Workbench，执行批量更新、删除时，报错。

1
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.	0.000 sec

MySQL Workbench 默认阻止这种批量修改删除操作，是为了防止我们由于误操作而导致的全表数据更新，因此强制我们必须使用有效的 where 语句来限制。

临时解决

可在 MySQL Workbench 查询窗口执行以下语句临时支持批量修改和删除：

1
set sql_safe_updates = 0;

但重启 MySQL Workbench 后会失效。

永久解决

修改配置，其配置项如下（edit -> preference -> SQL Editor -> Safe Updates(rejects UPDATEs and DELETEs with no restrictions)）：

该项默认是勾选状态，取消勾选，重启 MySQLWorkbench 生效。不过，如此设置之后，建议 MySQL Workbench 只连接开发和测试环境，不要连接生产环境以避免灾难性失误。

时间函数

  1. 将时间戳格式化为能看懂的时间格式

    1
    FROM_UNIXTIME(created, '%Y-%m-%d %H:%i:%s') as createdF

  2. 返回当前时间的UNIX时间戳

    1
    2
    select UNIX_TIMESTAMP(NOW()) from dual;
    select unix_timestamp() from dual;

MYSQL 事件

  1. 查看 event_scheduler 状态

    1
    show variables like '%sche%';

  2. 修改 event_scheduler 状态

    1
    set global event_scheduler=1;

    这种方式开启后，重启 MySQL 后，JOB 功能又 OFF 了，所以，还需要修改配置文件，以开启 JOB 功能。开启方法：在 my.cnf 添加 mysqlid event_scheduler=ON

  3. 创建 MYSQL 事件
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    DELIMITER $$
    CREATE event event_tb_push_data_backup
    ON SCHEDULE
    EVERY 5 MINUTE
    STARTS NOW()
    DO
    BEGIN
        CALL event_tb_push_data_backup();
    END;
    DELIMITER ;

mysql 修改自增列的起始值

1
alter table tableName AUTO_INCREMENT=100000066;

mysql 超时时间设置

设置全局变量 connect_timeout 为12小时（12*3600=43200）

1
2
MySQL [(none)]> SET GLOBAL connect_timeout = 43200;
Query OK, 0 rows affected (0.00 sec)

再次查看，确认参数是否修改成功

1
2
3
4
5
MySQL [(none)]> show global variables like '%timeout%';
+----------------------------------------+--------------+
| Variable_name | Value |
+----------------------------------------+--------------+
| connect_timeout | 43200 |

关于 information_schema 库

information_schema是一个虚拟数据库，并不物理存在，在select的时候，从其他数据库获取相应的信息。

以下是information_schema数据库中的表

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
66
67
68
69
70
mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_LOCK_WAITS                     |
| INNODB_SYS_TABLESTATS                 |
| INNODB_CMP                            |
| INNODB_METRICS                        |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_INDEXES                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_CMPMEM                         |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_BEING_DELETED               |
| INNODB_SYS_TABLESPACES                |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_SYS_TABLES                     |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_FT_CONFIG                      |
+---------------------------------------+
59 rows in set (0.00 sec)

在 information_schema 库中查看索引信息

1
2
select * from information_schema.INNODB_SYS_INDEXES where name='sellerID';
show index from crmErrLogOuter;

在 information_schema 库中查看表信息

1
SELECT * FROM information_schema.TABLES WHERE table_name ='crmErrLogOuter' and table_schema='crm';

查询表中数据超过 1000 行的表

1
2
3
4
select table_schema,table_name,table_rows from information_schema.tables 
where table_rows > 1000 
and table_schema != 'performance_schema'
order by table_rows desc;

查询所有没有主键的表

1
2
3
4
5
6
SELECT t.table_schema, t.table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc ON t.table_schema = tc.table_schema AND t.table_name = tc.table_name AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL
AND t.table_type = 'BASE TABLE'
and t.table_schema !='performance_schema';

查询 5 个最大表

1
2
3
SELECT table_schema, table_name, concat(round(data_length/(1024*1024),2),'M') data_length   
FROM information_schema.TABLES
ORDER BY data_length DESC LIMIT 5;

查看连接情况

1
select * from performance_schema.hosts;

可以肯定的是，版本 5.1.73 没有库：performance_schema，版本 5.6.26 有。

查看 MySQL 版本

  1. 登陆 mysql，进入命令模式
    • status;
    • select version();

  2. 利用 mysql –help

    1
    2
    # mysql --help | grep Distrib
    mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

  3. 包管理工具（redhat 系列）

    1
    2
    # rpm -qa|grep mysql 
    php-mysql-5.3.3-46.el6_6.x86_64

查看 mysql 现在已提供什么存储引擎

1
mysql> show engines;

查看 mysql 当前默认的存储引擎

1
mysql> show variables like '%storage_engine%';

提升插入速度

参考：MYSQLinsert速度过慢

MY SQL insert 速度过慢的解决方法

最近在用MySQL做存储，测试中发现插入数据太慢了，插入速度只有20-30 条/秒，后来查资料后，将MySQL的1个参数：innodb_flush_log_at_trx_commit，从 1 改为了 0（修改方法，可以直接修改 my.ini(windows)/my.cnf(linux) 中的对应参数，似乎用命令直接设置没有效果），插入速度就提升到了3000+每S。

MySQL的文档，对这个参数的描述如下：
If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

如果transaction commit的频率非常高，1秒钟内会进行很多次的话，可以考虑将其设置为0，但这样就要承担数据库Crash后，1秒内未存储到数据库数据丢失可能的风险。

1
set innodb_flush_log_at_trx_commit=0;

decimal 精度

decimal(4,2)，整数部分只能输入两位数，小数部分只能输入两位，也就是说，小数加整数的位数等于第一位精度，在这里是 4。

多表关联 update

1
2
3
4
update courseDetail cd 
inner join course c on c.courseID = cd.courseID
inner join organization org on org.orgID = c.orgID
set cd.classPlace = org.address;

密码强度

mysql5.7 默认安装了密码安全检查插件（validate_password），默认密码检查策略要求密码必须包含：大小写字母、数字和特殊符号，并且长度不能少于8位。否则会提示以下 错误

1
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

必须先改默认密码，才能做别的操作。
参考：CentOS7 安装 MySQL-5.7.20

查看错误日志文件路径

1
2
3
4
5
6
7
mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)

查看表结构

1
desc mysql.user;

mysql 8.x 查看存储过程

查看存储过程的创建代码

1
show create procedure proc_name;

生成某个数据库的所有存储过程的【查看存储过程创建代码】，同时也就知道了该数据库一共有多少个存储过程。

1
2
3
SELECT CONCAT('show create ' ,ROUTINE_TYPE  , ' `', ROUTINE_SCHEMA  ,'`.`', `SPECIFIC_NAME`,'`;') showSql, ROUTINE_SCHEMA, ROUTINE_TYPE,SPECIFIC_NAME 
FROM information_schema.`ROUTINES`
WHERE `ROUTINE_TYPE` ='PROCEDURE' and `ROUTINE_SCHEMA` ='your_db_name';

查看函数的创建代码

1
show create function func_name;

mysqldump 导出数据

  1. 导出某库中的多表，表结构和数据

    1
    mysqldump --default-character-set=utf8 -h192.168.1.130 -uroot -proot schemaName tableName1 tableName2 tableName3 > d:/schemaName.sql

  2. 某库中的全部表结构和数据

    1
    mysqldump --default-character-set=utf8 -h127.0.0.1 -uroot -proot -B schemaName > d:/schemaName.sql

  3. 导出某表，带条件

    1
    mysqldump --default-character-set=utf8 -h127.0.0.1 -uroot -proot schemaName tableName1 --where="id=80" > d:/schemaName.sql

导入数据

  1. 用 source 命令导入数据

    1
    2
    3
    4
    # 先连接上 mysql 再操作
    mysql> use rate;
    mysql> set names utf8mb4; -- 很重要，这样可以保证中文不会乱码
    mysql> source D:\dev.workspace\sql.data\rt_top_tmc_message_queue_crm_his_20170723.sql

  2. 用 < 的方式直接导入数据

    1
    mysql -hlocalhost -uroot -proot schemaName < new.sql --default-character-set=utf8mb4

授权

远程连接权限设置

允许 IP 为 192.168.1.89 的主机以 jiraUser 的身份连接所有库（*），密码是 JiraUserAnDyDoCkEr123

1
2
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sjiraUer'@'192.168.1.89' IDENTIFIED BY 'JiraUserAnDyDoCkEr123' WITH GRANT OPTION;
mysql> flush privileges;

查看 MYSQL 数据库用户远程连接权限

1
2
3
4
5
6
7
8
9
10
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS user FROM mysql.user;
+-------------------------------+
| user                          |
+-------------------------------+
| User: 'rate'@'%';             |
| User: 'root'@'127.0.0.1';     |
| User: 'root'@'192.168.0.168'; |
| User: 'redmine'@'localhost';  |
| User: 'root'@'localhost';     |
+-------------------------------+

2019-06-10 补充

或者用以下语句更直接

1
2
3
4
5
6
7
8
9
10
mysql> select host,user from mysql.user;
+---------------+---------------+
| host          | user          |
+---------------+---------------+
| 192.168.0.101 | admin         |
| 192.168.0.168 | admin         |
| localhost     | mysql.session |
| localhost     | mysql.sys     |
| localhost     | root          |
+---------------+---------------+

直接修改授权 IP

1
2
3
mysql> update mysql.user set host='192.168.1.89' where host='192.168.0.101';
Query OK, 1 row affected (0.33 sec)
Rows matched: 1  Changed: 1  Warnings: 0

删除授权

1
2
3
4
Delete FROM mysql.user Where User="root" and Host="192.168.0.168";
``` 

# CentOS7 忘记 mysql5.7 root 密码，重置密码

vim /etc/my.cnf

1
2
3
4
在 `[mysqld]` 节点的最后，添加：`skip-grant-tables`
重启 mysql：`systemctl restart mysqld`

登录并修改MySQL的 root 密码，直接输入 mysql 并回车：

[root@AndyCentOS7Basic ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update mysql.user set authentication_string=password(‘your new password’) where user=’root’;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
其中
- 修改密码：`update mysql.user set authentication_string=password('your new password') where user='root';`
- 刷新权限：`flush privileges;`

编辑mysql配置文件，将之前添加的skip-grant-tables去掉。
重启 mysql：`systemctl restart mysqld`

# mysql8 忘记密码
跟 mysql5.7的操作步骤差不多，只是在修改密码的语句上有差别。
mysql8 修改密码的语句为：`alter user 'root'@'%' IDETIFIED BY '新密码';`
在修改之前，用`select host,user from user;`查一下已有用户。

mysql>use mysql;
mysql>select host,user from user;
+———–+——————+
| host | user |
+———–+——————+
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+———–+——————+
mysql> flush privileges;
mysql> alter user ‘root‘@’%’ IDETIFIED BY ‘your new password’;
mysql> exit;

1
2
3
4
5

# mysql8.0.11 导数据报错：Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
## 现象
- windows 10 的 mysql 版本为 8.0.11，导出服务器上的 5.7.20 版本的数据，得到这个错。
- 换用 secureCRT 直接操作服务器上的 mysql（5.7.20），顺利导出。

C:\Users\AndyChen>mysqldump –default-character-set=utf8mb4 -h192.168.1.88 -uadmin -paAdDmMiInN1!1!0)3# mysqlDatabase > c:/workspace/mysqlDatabase.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.”number-of-buckets-specified”‘)
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = ‘mysqlDatabase’ AND TABLE_NAME = ‘sys_menu’;’: Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109)

1
2
3
4
5
6

在网上查了一下，原来 mysql8.0 之前的版本是没问题的。

## 解决办法
在导出语句添加：`--column-statistics=0`
格式为：

mysqldump –column-statistics=0 –default-character-set=utf8mb4 -h192.168.1.88 -uadmin -paAdDmMiInN1!1!0)3# mysqlDatabase > c:/workspace/ginadmin.sql

1
2
3
4
5
6
7
8
9
10
11
12
测试无误~~

# 阿里云数据库，RDS 不能保存微信特殊字符 emoji
原因：数据库编码为utf-8不支持ios的emoji

在 RDS 实例控制台 -> 参数设置 , 的【character_set_server】项，将 utf8 改为：utf8mb4，改好后，点击【提交参数】。

注意：
* 如果在创建数据库之前没有设置该参数，即使创建数据库时指定为 utf8mb4，也无济于事。
* 该参数修改后, 仅对开启高权限账号的实例后来创建的数据库有效. 对当前数据库无效。

可对字段单独修改，如：

ALTER TABLE member CHANGE wxNickname wxNickname varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

# Mysql中字符串正确的连接方法
在Mysql中，使用“+”进行字符连接时，mysql会尝试将字段值转换为数字类型（如果转换失败，就当做数字0处理）。如’1abc’+’22′，mysql将“1abc”转成数字1在进行运算；将“abc”当做0处理。 

在MYSQL中进行字符串的拼接要使用CONCAT函数，CONCAT函数支持一个或者多个参数，参数类型可以为字符串类型也可以是非字符串类型，对于非字符串类型的参数MYSQL将尝试将其转化为字符串类型，CONCAT函数会将所有参数按照参数的顺序拼接成一个字符串做为返回值。

参考：https://www.cnblogs.com/zhengah/p/5199843.html

# 生成批量修改备注的语句
mysql 批量清空字段的备注 comment
里面有个TABLE_COMMENT，就是表的注释，想批量修改直接update这个表的这个字段；
同理，字段的注释在information_schema.COLUMNS这个表中。

SELECT
concat(
‘alter table ‘,
table_schema, ‘.’, table_name,
‘ modify column ‘, column_name, ‘ ‘, column_type, ‘ ‘,
if(is_nullable = ‘YES’, ‘ ‘, ‘not null ‘),
if(column_default IS NULL, ‘’,
if(
data_type IN (‘char’, ‘varchar’)
OR
data_type IN (‘date’, ‘datetime’, ‘timestamp’) AND column_default != ‘CURRENT_TIMESTAMP’,
concat(‘ default ‘’’, column_default,’’’’),
concat(‘ default ‘, column_default)
)
),
if(extra is null or extra=’’,’’,concat(‘ ‘,extra)),
‘ comment ‘’’’;’ – 本句会将备注设置为空
– ‘ comment ‘’’, column_comment, ‘’’;’ – 本句会带出原来的备注
) s
FROM information_schema.columns
WHERE table_schema = ‘mis’ AND table_name = ‘news’;

1
2
# com.alibaba.druid.pool.DruidDataSourceStatLoggerImpl
日志，会不断的打印，如何屏蔽掉？

14:35:48,944 <com.alibaba.druid.pool.DruidDataSourceStatLoggerImpl> INFO [Druid-ConnectionPool-Log-1351788685]: {“url”:”jdbc:mysql://localhost:3306/csist_mis?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&socketTimeout=20000”,”dbType”:”mysql”,”name”:”DataSource-1351788685”,”activeCount”:0,”poolingCount”:1,”connectCount”:0,”closeCount”:0}

1
2
3
4
5
参见官网文档：https://www.bookstack.cn/read/Druid/70242a8b751bf5ca.md
取消 timeBetweenLogStatsMillis 的配置即可取消定时打印日志

# Caused by: com.mysql.cj.exceptions.CJException: Public Key Retrieval is not allowed
连接 mysql 报错

Caused by: com.mysql.cj.exceptions.CJException: Public Key Retrieval is not allowed

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

连接数据库的url中，加上allowPublicKeyRetrieval=true参数，经过验证解决该问题。

# MySQL Tinyint的取值范围是
在 MySQL 的数据类型中，Tinyint的取值范围是：带符号的范围是-128到127。无符号的范围是0到255。

[见官方《MySQL 5.1参考手册》](http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#numeric-types)

# MYSql 日期类型精度
- TIMESTAMP(3)与 DATETIME(3)意思是保留3位毫秒数。
- TIMESTAMP(6)与 DATETIME(6)意思是保留6位毫秒数。
- 通过 MyBatis 更新带毫秒的时间，结果还是只到秒。
- 手工更新，也还是只精确到秒。

暂时通过添加字符类型的字段来保留到毫秒级的时间数据。

# PowerDesigner 连接 MySQL下载 ODBC 驱动
[oracle mysql ODBC driver](https://dev.mysql.com/downloads/connector/odbc/)
MYSQL Connector/ODBC 提供了两个版本，一个是 ANSI driver  和 Unicode driver 两个版本。  
Unicode driver 版本提供了更多字符集的支持，也就是提供了多语言的支持。而ANSI driver 版本是只针对有限的字符集的范围。

# mysql 慢查询优化
以下是 C# 里的 EF 查询表达式

// 不加 will_rate_time >= willRateTimeStart，在有接近300万条记录时，查询非常慢，每次查询需要扫描大于一半的记录，达到160多万条。加上之后，跨度只有一天时间后，扫描行数变成5万左右。
// 不加 will_rate_time >= willRateTimeStart，直接在 MySqlWorkbench 上查询，花了144秒（加上limit 15000)，而在开发环境查询，仅需要不到5秒时间。
// 不加之前，应用程序一直报错：An error occurred while reading from the store provider’s data reader. See the inner exception for details.
// 这是超时导致。
// will_rate_time 字段有索引
var query = from om in ctx.rt_on_messages.AsNoTracking()
where om.is_rate_switch_on == 1
&& om.is_seller_rate.Value == 0
&& om.is_session_key_null == 0
&& om.rate_failed_times < MaxRateFailedTimes
&& om.will_rate_time >= willRateTimeStart
&& om.will_rate_time < willRateTimeEnd
&& om.rate_result > 0
//&& arrTid.Contains(om.tid)
//&& om.will_rate_time > -1
select om;

//.OrderByDescending(o => o.publish_time)
return query.Take(MessageTopRecords).ToList<rt_on_messages>();

1
查看是否开启慢查询

mysql> show variables like ‘%log_slow_queries%’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| log_slow_queries | ON |
+——————+——-+
1 row in set (0.00 sec)

mysql>

1
2
开启慢查询非常简单, 操作如下:
Linux下找到mysql的配置文件my.ini, 在mysqld下方加入慢查询的配置语句(注意:一定要在[mysqld]下的下方加入)

log-slow-queries=/mnt/mysqlLog/slowQueries.log
long_query_time=10

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
log-slow-queries: 代表MYSQL慢查询的日志存储目录, 此目录文件一定要有写权限；
long_query_time: 最长执行时间.
MSYQL将记录下所有执行时间超过long_query_time秒的SQL语句, 此处为测试时间, 时间不应太小最好在5-10秒之内, 当然可以根据自己的标准而定
MySQL 慢查询的相关参数解释：

* slow_query_log    ：是否开启慢查询日志，1表示开启，0表示关闭。
* log-slow-queries  ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
* slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
* long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
* log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
* log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

慢查询日志配置

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启，如下所示：

mysql> show variables like ‘%slow_query_log%’;
+———————+———————————+
| Variable_name | Value |
+———————+———————————+
| slow_query_log | ON |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+———————+———————————+

mysql> set global slow_query_log=1;
mysql> set global long_query_time=4;
mysql> show variables like ‘long_query_time%’;

1
2
3
# C# 连接 MySQL 异常
原因是MySQL 压力太大，有慢查询，cpu 使用率接近100%。  
以下是 C# 捕获的异常信息。

– ===================================
时间: 2017/6/29 11:27:32
错误号: 1000
标题: An error occurred while reading from the store provider’s data reader. See the inner exception for details.
内容: An error occurred while reading from the store provider’s data reader. See the inner exception for details.
EntityFramework
在 System.Data.Entity.Core.Common.Internal.Materialization.Shaper1.HandleReaderException(Exception e) 在 System.Data.Entity.Core.Common.Internal.Materialization.Shaper1.StoreRead()
在 System.Data.Entity.Core.Common.Internal.Materialization.Shaper1.SimpleEnumerator.MoveNext() 在 System.Data.Entity.Internal.LazyEnumerator1.MoveNext()
在 System.Collections.Generic.List1..ctor(IEnumerable1 collection)
在 System.Linq.Enumerable.ToListTSource
在 Maile360.Rate.DAL.OnMessagesDAL.GetOnMessagesList()
在 Maile360.Rate.Logic.Common.OnMessagesDealLogic.Run()
在 Maile360.Rate.RealtimeService.OrdersToBeRateService.Go()
出现错误的位置: OnMessagesWaitingUpdateService
错误级别: urgency
– =====================================

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# Windows 下 MySql 设置表名区分大小写
windows 下，默认表名不区分大小写但字段大小写与创建时一致。
在 mysql 主目录下修改 my.ini，在配置项 [mysqld] 下添加：lower_case_table_names=0

换最新版本再试

`mysql 5.6` 添加上面的配置后，启动失败，提示1067

`lower_case_table_names`，参数含义如下：
1. 0：使用`CREATE TABLE`或`CREATE DATABASE`语句指定的大小写字母在硬盘上保存表名和数据库名。名称比较对大小写敏感。在大小写不敏感的操作系统如windows或Mac OS x上我们不能将该参数设为0，如果在大小写不敏感的文件系统上将`--lowercase-table-names`强制设为0，并且使用不同的大小写访问MyISAM表名，可能会导致索引破坏。
1. 1：表名在硬盘上以小写保存，名称比较对大小写不敏感。MySQL将所有表名转换为小写在存储和查找表上。该行为也适合数据库名和表的别名。该值为Windows的默认值。
1. 2：表名和数据库名在硬盘上使用`CREATE TABLE`或`CREATE DATABASE`语句指定的大小写字母进行保存，但MySQL将它们转换为小写在查找表上。名称比较对大小写不敏感，即按照大小写来保存，按照小写来比较。注释：只在对大小写不敏感的文件系统上适用! innodb表名用小写保存。

# PowerDesigner 生成数据库时的列中文注释乱码问题的设置方法
`PowerDesinger->Database->Generate Database->Format->Encoding` 改成 utf8
![][/img/powerDesignDatabaseGenerationFormatToUtf8.jpg]

# mysql The server time zone value

The server time zone value ‘Öйú±ê׼ʱ¼ä’ is unrecognized or represents more than one time zone.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
在连接后面加上：`?useSSL=false&serverTimezone=GMT&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&socketTimeout=20000`

# MySQL 获取当前年
`SELECT DATE_FORMAT(NOW(), '%Y') as year;`

# MySQL int 后面的数字与前导零填充
字段加属性：zerofill，如：`current_value        int(6) unsigned zerofill  comment '当前值',`

# ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'
将 MySQL 8.X 的 mysqldump 文件导入 MySQL 5.7 报的错，8.x 的字符编码设置为 utf8mb4 了。

## 解决
打开sql文件，将文件中的所有 utf8mb4_0900_ai_ci 替换为 utf8_general_ci，utf8mb4 替换为 utf8

# 关联更新
关联多张表，只更新满足条件的记录

update ts_shop_express se
inner join ts_express_tpl_back_7 tpl on se.cp_code = tpl.cp_code and se.url = tpl.url
inner join ts_express_tpl tpl_new on tpl_new.id = tpl.id
set se.url = tpl_new.url
`