Mysql
目录
MYSQL常用jdbc参数配置说明
参数名称 | 参数说明 | 缺省值 | 最低版本要求 |
---|---|---|---|
useUnicode | 是否使用Unicode字符集,如果参数characterEncoding设置为gb2312或gbk,本参数值必须设置为true | false | 1.1g |
characterEncoding | 当useUnicode设置为true时,指定字符编码。比如可设置为gb2312或gbk | false | 1.1g |
autoReconnect | 当数据库连接异常中断时,是否自动重新连接 | false | 1.1 |
autoReconnectForPools | 是否使用针对数据库连接池的重连策略 | false | 3.1.3 |
failOverReadOnly | 自动重连成功后,连接是否设置为只读 | true | 3.0.12 |
maxReconnects | autoReconnect设置为true时,重试连接的次数 | 3 | 1.1 |
initialTimeout | autoReconnect设置为true时,两次重连之间的时间间隔,单位:秒 | 2 | 1.1 |
connectTimeout | 和数据库服务器建立socket连接时的超时,单位:毫秒。 0表示永不超时,适用于JDK 1.4及更高版本 | 0 | 3.0.1 |
socketTimeout | socket操作(读写)超时,单位:毫秒。 0表示永不超时 | 0 | 3.0.1 |
serverTimezone | 覆盖时区的检测/映射。当服务器的时区未映射到Java时区时使用 | 3.0.2 | |
allowMultiQueries | 可以在sql语句后携带分号,实现多语句执行。(可以使得sql语句中有多个insert或者update语句) | ||
nullCatalogMeansCurrent | 指定库涉及表(true:jdbcurl中指定的数据库,false:mysql下全部的库;默认值根据mysql驱动版本认定) | 从mysql-connector-java 5.x 版本起,nullCatalogMeansCurrent 属性由原来的默认true改为了false。6.0之后 nullCatalogMeansCurrent 默认又改为true。8.0后默认又改为false。 |
命令行
连接格式
mysql -h主机地址 -u用户名 -p用户密码 -P端口
登录本地mysql
mysql -uroot –p
登录本地mysql并执行sql
mysql -u root -p"Mysql1234%^&*" -e "show variables like '%datadir%';"
登录远程mysql并执行sql脚本
mysql -h远程服务器地址 –u用户名 –p密码 –D数据库<sql脚本文件路径
初始密码
grep 'temporary password' /var/log/mysqld.log
修改密码(不知旧密码)
my.cnf,Linux下默认路径为/etc/my.cnf
[mysqld]#下添加
skip-grant-tables=1 #不用验证MySQL修改密码
- 方式1 mysqladmin
mysqladmin -u 用户名 -p 旧密码 password 新密码
mysqladmin -uroot -p"1234%^&*" password "Mysql1234%^&*"
- 方式2 SET PASSWORD
set password for 用户名@localhost = password('新密码');
set password for root@localhost = password('Mysql%^&*1234');
- 方式3用UPDATE直接编辑user表(Mysql5.7之前)
use mysql;
update user set password=password('Mysql1234%^&*') where user='root' and host='localhost';
flush privileges;
- 方式4 ALTER
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mysql%^&*1234';
远程主机访问授权
- 允许任何主机远程连接访问
grant ALL PRIVILEGES ON *.* TO root@'%' identified by "123456";
grant select on 数据库.* to 用户名@'%' identified by “密码”;
grant * on hsf_hibiz.* to hsa_admin@'%' Identified by 'abc' WITH GRANT OPTION;
grant select,insert,update,delete on *.* to hsa_admin@'%' Identified by 'abc';
- 只允许本机连接访问
grant select on 数据库.* to 用户名@登录主机 identified by “密码”;
grant select,insert,update,delete on hsf_hibiz.* to hsa_admin@localhost identified by “hsa_admin”;
查看Server版本
Select version();
查看数据存储路径
show variables like '%datadir%';
启动关闭
- 启动服务
service mysql start
systemctl start mysqld.service
- 关闭服务
service mysql stop
systemctl stop mysqld.service
- 重启服务
service mysql restart
systemctl restart mysqld.service
遇到锁表快速解决办法
当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;
当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
批量删除事务表中的事务
通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件,然后执行临时文件中生成的指令。
SELECT concat('KILL ',id,';') FROM information_schema.processlist p INNER JOIN information_schema.INNODB_TRX x ON p.id=x.trx_mysql_thread_id WHERE db='test';
查看表锁情况
SHOW GLOBAL STATUS LIKE 'table_locks%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 90 |
| Table_locks_waited | 0 |
+-----------------------+-------+
- Table_locks_immediate 立即获得表锁请求的次数
- Table_locks_waited 无法立即获得对表锁的请求的次数,需要等待。这个值过高说明性能可能出现了问题,并影响连接的释放
Timeout详细解析
show variables like ‘%timeout%’ ;
mysql性能
mysqld.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
#数据库允许的最大连接数
max_connections=1000
#控制一个数据包发送的任何参数的最大大小
max_allowed_packet=200M
#控制是否需要进行域名解析来获取客户端的主机名
skip-name-resolve
#用于缓存Innodb存储引擎表的数据、索引等的最大缓存区大小,是数据库性能影响最大的一个参数
innodb_buffer_pool_size=16G
#Mysql读入缓冲区大小,对表进行顺序扫描的请求将分配一个读入缓冲区
read_buffer_size=4M
#用于存放join查询中间结果的缓存大小
join_buffer_size=8M
#用于存放排序数据的缓存大小,超过这个大小就会使用文件排序
sort_buffer_size=8M
# Mysql 随机 Query 缓冲区大小,当按任意顺序读取行时,将分配一个随机读取缓冲区。如进行排序查询时,Mysql 会首先扫描该缓冲,避免磁盘搜索,提高查询速度( 默认 256K ,该缓冲也是为每线程分配 )
read_rnd_buffer_size=4M
#临时表大小
tmp_table_size=32M
myisam_sort_buffer_size=32M
key_buffer_size=128M
常用语句
显示所有数据库
show databases
选定某个数据库
use dbname
显示某个库中的所有表
SHOW TABLES [FROM db_name]
查看表结构
desc table
查看建表SQL
SHOW CREATE TABLE tbl_name
查看表索引
SHOW INDEX FROM tbl_name [FROM db_name]
查看表主键
show keys from tblname;
查看超时时间
show variables like '%timeout%';
查看慢sql是否开启
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | OFF |
| slow_launch_time | 2 |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 279 |
+---------------------+-------+
配置中关闭了记录慢查询(最好是打开,方便优化),超过2秒即为慢查询,一共有279条慢查询
列出用户正在运行的线程
SHOW PROCESSLIST
列出某用户权限
SHOW GRANTS FOR user
列出 MySQL 系统环境变量
SHOW VARIABLES
列出线程情况
SHOW STATUS LIKE 'Threads%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 1 |
| Threads_connected | 217 |
| Threads_created | 29 |
| Threads_running | 88 |
+-------------------+--------+
SHOW VARIABLES LIKE 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 10 |
+-------------------+-------+
- Threads_cached 线程在缓存中的数量
- Threads_connected 当前打开的连接数
- Threads_created 创建用于处理连接的线程数。
- Threads_running 未休眠的线程数
注意:如果Threads_created大,则可能要增加thread_cache_size值。缓存未命中率可以计算为Threads_created / Connections
列出总连接数
SHOW VARIABLES LIKE "max_connections" ;
列出单个用户的连接数最大值,即并发值
SHOW VARIABLES LIKE 'max_user_connections';
列出 DB Server 状态
SHOW STATUS
列出字段完整属性
SHOW FULL FIELDS FROM tbl_name [FROM db_name]
列出字段及详情
SHOW FULL COLUMNS FROM tbl_name [FROM db_name]
列出资料表字段
SHOW COLUMNS FROM tbl_name [FROM db_name]
列出数据表及表状态信息
SHOW TABLE STATUS [FROM db_name]
开启/关闭失去了记录日志
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'OFF';
查看执行sql
select * from mysql.general_log
查看数据库容量
SELECT
table_schema AS '数据库',
sum( table_rows ) AS '记录数',
sum(TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
sum(TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
information_schema.TABLES
GROUP BY
table_schema
ORDER BY
sum( data_length ) DESC,
sum( index_length ) DESC;
服务器执行sql
[root@localhost ~]# for i in {3..1024};do mysql -h 172.20.0.124 -P2883 -u dr_write@yh_02#hndsj_yh -p'1h5.Y0lTjr_E0nGhJl' -e "update /*+ PARALLEL(16),READ_CONSISTENCY(WEAK),query_timeout(10000000000) */ setlcent_clc_db.psn_trt_info_d partition (p$i) set vali_flag = '0' where PSN_SETL_CUM_SN in(select PSN_SETL_CUM_SN from data_mid_02_db.PSN_SETL_CUM_SN) and vali_flag = '1';";sleep 2;done
IF表达式
IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。
IFNULL(expr1,expr2)
假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。
DATE_FORMAT
DATE_FORMAT(date,format)
format参数
限定符 | 含义 |
---|---|
%a | 三个字符缩写的工作日名称,例如:Mon, Tue, Wed,等 |
%b | 三个字符缩写的月份名称,例如:Jan,Feb,_Mar_等 |
%c | 以数字表示的月份值,例如:1, 2, 3…12 |
%D | 英文后缀如:0th, 1st, _2nd_等的一个月之中的第几天 |
%d | 如果是1个数字(小于10),那么一个月之中的第几天表示为加前导加0, 如:00, 01,02, …31 |
%e | 没有前导零的月份的日子,例如:1,2,… 31 |
%f | 微秒,范围在000000..999999 |
%H | 24小时格式的小时,前导加0,例如:00,01..23 |
%h | 小时,12小时格式,带前导零,例如:01,02 … 12 |
%I | 与%h相同 |
%i | 分数为零,例如:00,01,… 59 |
%j | 一年中的的第几天,前导为0,例如,001,002,… 366 |
%k | 24小时格式的小时,无前导零,例如:0,1,2 … 23 |
%l | 12小时格式的小时,无前导零,例如:0,1,2 … 12 |
%M | 月份全名称,例如:January, February,…December |
%m | 具有前导零的月份名称,例如:00,01,02,… 12 |
%p | AM或PM,取决于其他时间说明符 |
%r | 表示时间,12小时格式hh:mm:ss AM或PM |
%S | 表示秒,前导零,如:00,01,… 59 |
%s | 与%S相同 |
%T | 表示时间,24小时格式hh:mm:ss |
%U | 周的第一天是星期日,例如:00,01,02 … 53时,前导零的周数 |
%u | 周的第一天是星期一,例如:00,01,02 … 53时,前导零的周数 |
%V | 与%U相同,它与%X一起使用 |
%v | 与%u相同,它与%x一起使用 |
%W | 工作日的全称,例如:Sunday, Monday,…, Saturday |
%w | 工作日,以数字来表示(0 = 星期日,1 = 星期一等) |
%X | 周的四位数表示年份,第一天是星期日; 经常与%V一起使用 |
%x | 周的四位数表示年份,第一天是星期日; 经常与%v一起使用 |
%Y | 表示年份,四位数,例如2000,2001,…等。 |
%y | 表示年份,两位数,例如00,01,…等。 |
%% | 将百分比(%)字符添加到输出 |
常用的日期格式字符串
DATE_FORMAT字符串 | 格式化日期 |
---|---|
%Y-%m-%d | 2017/4/30 |
%e/%c/%Y | 4/7/2013 |
%c/%e/%Y | 7/4/2013 |
%d/%m/%Y | 4/7/2013 |
%m/%d/%Y | 7/4/2013 |
%e/%c/%Y %H:%i | 4/7/2013 11:20 |
%c/%e/%Y %H:%i | 7/4/2013 11:20 |
%d/%m/%Y %H:%i | 4/7/2013 11:20 |
%m/%d/%Y %H:%i | 7/4/2013 11:20 |
%e/%c/%Y %T | 4/7/2013 11:20 |
%c/%e/%Y %T | 7/4/2013 11:20 |
%d/%m/%Y %T | 4/7/2013 11:20 |
%m/%d/%Y %T | 7/4/2013 11:20 |
%a %D %b %Y | Thu 4th Jul 2013 |
%a %D %b %Y %H:%i | Thu 4th Jul 2013 11:20 |
%a %D %b %Y %T | Thu 4th Jul 2013 11:20:05 |
%a %b %e %Y | Thu Jul 4 2013 |
%a %b %e %Y %H:%i | Thu Jul 4 2013 11:20 |
%a %b %e %Y %T | Thu Jul 4 2013 11:20:05 |
%W %D %M %Y | Thursday 4th July 2013 |
%W %D %M %Y %H:%i | Thursday 4th July 2013 11:20 |
%W %D %M %Y %T | Thursday 4th July 2013 11:20:05 |
%l:%i %p %b %e, %Y | 7/4/2013 11:20 |
%M %e, %Y | 4-Jul-13 |
%a, %d %b %Y %T | Thu, 04 Jul 2013 11:20:05 |
字符串转数字
- 字段值+0
select psn_no + 0 from setl_test_d
- cast(字段名 as 转换的类型)
select cast(psn_no as signed) from setl_test_d
- convert
select convert(psn_no, signed) from setl_test_d
json中字段值的提取
语法
JSON_EXTRACT(json, path, ...)
JSON_EXTRACT()
函数返回JSON
文档中由路径表达式匹配的所有的值。如果路径表达式匹配了一个值,则返回该值,如果路径表达式匹配了多个值,则返回一个包含了所有值的数组。
如果存在以下的情况, JSON_EXTRACT() 函数将返回 NULL:
如果 JSON 文档中不存在指定的路径。
如果任意一个参数为 NULL。
JSON_EXTRACT() 函数将在以下情况下返回错误:
如果参数 json 不是有效的 JSON 文档,MySQL 将会给出错误。您可以使用 JSON_VALID() 验证 JSON 文档的有效性。
如果参数 path 不是有效的路径表达式, MySQL 将会给出错误。
示例
# 如何从数组中提取一个元素
SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]');
+------------------------------------------+
| JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]') |
+------------------------------------------+
| {"x": 3} |
+------------------------------------------+
# 如何从数组中提取多个元素
SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]');
+------------------------------------------------------------+
| JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]') |
+------------------------------------------------------------+
| [3, 2, 1] |
+------------------------------------------------------------+
# 如何从对象中提取一个节点
SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y');
+----------------------------------------------+
| JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y') |
+----------------------------------------------+
| [1, 2] |
+----------------------------------------------+
# 带有多个路径
SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y');
+-----------------------------------------------------+
| JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y') |
+-----------------------------------------------------+
| [1, [1, 2]] |
+-----------------------------------------------------+
获取所有表的主键
SELECT
tab.TABLE_SCHEMA,
col.table_name,
col.column_name
FROM
information_schema.table_constraints tab,
information_schema.key_column_usage col
WHERE
col.constraint_name = tab.constraint_name
AND col.table_name = tab.table_name
AND tab.constraint_type = 'primary key'
AND tab.TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys', 'sysdb' );
索取没有主键的表
SELECT
a.TABLE_SCHEMA,
a.TABLE_NAME
FROM
(
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys', 'sysdb' )) AS a
LEFT JOIN (
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys', 'sysdb' )) AS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
WHERE
b.TABLE_NAME IS NULL;