mysql工作中的小结
1 mysql 包含语句查询 （find_in_set, locate）
1 select * from user where id in ('1','2');
2 在存字段的时候 可以存 ids = '1,2,3,4'
查询的时候 直接使用 select * from user where id in (ids);
3 使用 find_in_set( ) 方法：
select * from `table1` where find_in_set(tables1.id, '1,2,3,4' )
# 使用locate(substr,str)函数，如果包含，返回>0的数，否则返回0
# 例子：判断site表中的url是否包含'http://'子串,如果不包含则拼接在url字符串开头
4 update site set url =concat('http://',url) where locate('http://',url)=0
2 mysql ifnull()函数
IFNULL(expr1,expr2)
# 如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2
# 例子:
SELECT IFNULL(1,0); -- 1
SELECT IFNULL(1/0,'hello'); -- hello
3 mysql case when then
SELECT
case -------------如果
when sex='1' then '男' -------------sex='1'，则返回值'男'
when sex='2' then '女' -------------sex='2'，则返回值'女'
else 0 -------------其他的返回'其他’
end -------------结束
from sys_user --------整体理解： 在sys_user表中如果sex='1'，则返回值'男'如果sex='2'，则返回值'女' 否则返回'其他’
4 mysql 将时间戳转换为标准格式的方法
select FROM_UNIXTIME(t3.createtime, '%Y-%m-%d %H:%i:%S') AS `创建时间`,
5 mysql 带有参数的储存过程
DELIMITER $$
USE `db1`$$
DROP PROCEDURE IF EXISTS `add_cost_permission`$$
CREATE DEFINER=`xxx`@`%` PROCEDURE `add_cost_permission`(
IN start_num INT,
IN end_num INT,
IN username VARCHAR(32)
)
COMMENT '批量增加成本数据权限 调用时传入起始,结束部门ID和用户名'
BEGIN
DECLARE i INT DEFAULT start_num;
WHILE i < end_num DO
INSERT INTO db1.`xxxxx`(dept_id,user_name)VALUES(i,username);
SET i = i+1;
END WHILE;
END$$
DELIMITER ;
6 mysql 查询某段时间 范围
1 select * from XX where date between 2017-03-10 07:00:00 and 2017-03-10 08:55:00 and record is null
2 UPDATE `xxx` SET inv_title = "皮特潘公司" WHERE uid = 122121 AND YEAR(inv_date) = 2018 AND MONTH (inv_date) = 6
7 mysql 里的 换行与回车 concat 与 replace
SELECT email,NAME, REPLACE ( GROUP_CONCAT( dep_name ), ',' , CONCAT ( CHAR(13), CHAR(13) )) AS dep_name FROM xxx
8 mysql 查看 数据库和表的详细信息
# 查看数据库表基本信息。
select * from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA = 'workflow' and information_schema.TABLES.TABLE_NAME = 'adcrm_company_payment_days' \G;
# 查看数据库的大小
select (sum(DATA_LENGTH) + sum(INDEX_LENGTH))/1024/1024 from information_schema.TABLES where TABLE_SCHEMA = 'workflow';
# 查看表的 更新状态
select TABLE_NAME, UPDATE_TIME from information_schema.TABLES where TABLE_SCHEMA = 'workflow' and information_schema.TABLES.TABLE_NAME = 'adcrm_company_payment_days' order by UPDATE_TIME desc limit 1;
# 查看表的索引
show index from adcrm_company_payment_days \G;
9 mysql 查看操作log
1 show variables like 'log_bin'; 查看是否使用 log
2 如果启用了，即ON，那日志文件就在mysql的安装目录的 data目录下。
10 mysql union all并集操作
select * from a union all select * from b
11 mysql 字符串截取 left right substring
SELECT left ( group_concat ( p.pay_time order by p.pay_time desc), LOCATE ( ',', group_concat ( p.pay_time order by p.pay_time desc) ) - 1 )
SELECT right ( group_concat ( p.pay_time order by p.pay_time desc), LOCATE ( ',', group_concat ( p.pay_time order by p.pay_time desc) ) - 1 )
SELECT SUBSTRING('www.yuanrengu.com', 9, 3) 从字符串的第9个字符开始，只取3个字符
SELECT SUBSTRING_INDEX('www.yuanrengu.com', '.', 2);
11 mysql 时间字段加上 on update , current timestamp （记录更新操作时间）
12 mysql 的 共享锁 和 排它锁
lock in share mode适用于两张表存在业务关系时的一致性要求
SELECT ... LOCK IN SHARE MODE 走的是 IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，
这样的话，其他session可以读取这些记录，也可以继续添加IS锁，
但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。
for update适用于操作同一张表时的一致性要求。
SELECT ... FOR UPDATE 走的是 IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，
其他session也就无法在这些记录上添加任何的S锁或X锁。
13 查看某个表中是否含有某个字段
SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'project' AND table_name ='adn_plan_daily_stats' AND column_name='un_pack';
14 查看MYSQL中 使用 CURRENT_TIMESTAMP为默认值的 字段, 表, 数据库
SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_DEFAULT = 'CURRENT_TIMESTAMP'
AND TABLE_SCHEMA LIKE "%ka%"
how-can-i-get-tables-which-using-default-current-timestamp-in-mysql
查看变量设置
SHOW VARIABLES LIKE 'wait_timeout';
# MySQL建立连接超时时间
SHOW VARIABLES LIKE 'max_allowed_packet' # Server接受的数据包大小
SHOW VARIABLES LIKE 'wait_timeout'; # 客户端连接空闲时间
SHOW VARIABLES LIKE 'max_connections'
# 数据库的最大连接数
SHOW GLOBAL VARIABLES LIKE "%connect%";
"character_set_connection" "utf8"
"collation_connection" "utf8_general_ci"
"connect_timeout" "60"
"disconnect_on_expired_password" "ON"
"init_connect" ""
"max_connect_errors" "1000000"
"max_connections" "2000"
"max_user_connections" "0"
"performance_schema_session_connect_attrs_size" "512"
SHOW GLOBAL STATUS LIKE "%connect%";
"Aborted_connects" "0"
"Connection_errors_accept" "0"
"Connection_errors_internal" "0"
"Connection_errors_max_connections" "0"
"Connection_errors_peer_address" "0"
"Connection_errors_select" "0"
"Connection_errors_tcpwrap" "0"
"Connections" "86003"
"Max_used_connections" "638"
"Performance_schema_session_connect_attrs_lost" "0"
"Ssl_client_connects" "0"
"Ssl_connect_renegotiates" "0"
"Ssl_finished_connects" "0"
"Threads_connected" "635"
SHOW GLOBAL VARIABLES LIKE "%thread%";
"innodb_purge_threads" "1"
"innodb_read_io_threads" "8"
"innodb_thread_concurrency" "0"
"innodb_thread_sleep_delay" "10000"
"innodb_write_io_threads" "8"
"max_delayed_threads" "20"
"max_insert_delayed_threads" "20"
"myisam_repair_threads" "1"
"performance_schema_max_thread_classes" "50"
"performance_schema_max_thread_instances" "4100"
"thread_cache_size" "4096"
"thread_concurrency" "10"
"thread_handling" "one-thread-per-connection"
"thread_stack" "262144"
SHOW GLOBAL STATUS LIKE "%thread%";
"Delayed_insert_threads" "0"
"Performance_schema_thread_classes_lost" "0"
"Performance_schema_thread_instances_lost" "0"
"Slow_launch_threads" "0"
"Threads_cached" "4"
"Threads_connected" "638"
"Threads_created" "642"
"Threads_running" "5"
SHOW VARIABLES LIKE '%timeout%';
"connect_timeout" "60"
"delayed_insert_timeout" "300"
"innodb_flush_log_at_timeout" "1"
"innodb_lock_wait_timeout" "50"
"innodb_rollback_on_timeout" "OFF"
"interactive_timeout" "86400"
"lock_wait_timeout" "31536000"
"net_read_timeout" "30"
"net_write_timeout" "60"
"rpl_semi_sync_master_timeout" "10000"
"rpl_stop_slave_timeout" "31536000"
"slave_net_timeout" "3600"
"wait_timeout" "28800"
