mysql sql记录

mysql连接命令 (登录)

#本地登录
/usr/local/mysql/bin/mysql -u root -p

mysql -u root -p

#连接host登录
mysql -h $host --port $port -u root -p$password

查看日志文件路径

#查看 slow log 路径
show variables like 'slow_query_log_file';

#查看 error log 路径
show variables like 'log_error';

设置数据库变量超时时间

show global variables like '%timeout%';

set global wait_timeout=40;
set global interactive_timeout=40;

设置root允许远程连接数据库

update mysql.user set user.Host='%' where user.User='root';

flush privileges;

设置远程账户并授权 (123456为账户密码)

#开放所有ip
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;

##限定具体ip
GRANT ALL PRIVILEGES ON db1.* TO 'root'@'192.168.1.101' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;

修改密码 (版本5.7)

#设置新密码并刷新权限
update mysql.user set authentication_string=password('root') where user='root' ;
flush privileges;

linux 重置密码 (版本5.7)

##关闭mysql服务
service mysql stop
 
##启用数据库维护模式
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

##注: --skip-grant-tables 跳过授权表   --skip-networking 跳过远程登录

##登录
mysql

#设置新密码并刷新权限
update mysql.user set authentication_string=password('root') where user='root' ;
flush privileges;

windows重置密码 (版本5.7)

##关闭mysql服务 (以管理员身份运行)
net stop mysql  

##启用数据库维护模式  (cd到bin目录)
mysqld --defaults-file="xxxx\my.ini" --skip-grant-tables

##注: --skip-grant-tables 跳过授权表 

#连接mysql设置新密码并刷新权限

#打开新cmd窗口cd到bin目录连接mysql / 使用数据库连接工具
mysql 

#设置密码 
update mysql.user set authentication_string=password('root') where user='root' ;
flush privileges;

##其他
 ###如果使用的为宝塔最好以宝塔上的root密码,然后在任务管理器关闭mysqld.exe,再通过宝塔进行启动

查询mysql版本

select version();

区分大小写查询(utf8_bin默认区分大小写)

select * from user where binary username ='Sa';

排查字符字段大小写不敏感问题

1.查看数据库排序规则:  
  utf8_general_ci (不区分大小写，宝塔创建的为utf8默认为此规则)  
  utf8_bin (区分大小写) 

2.查看建表语句
show create table user
show create table domain_result

##以下user中user_name支持区分大小写而domain_result中字符不支持区分大小写

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_date` datetime DEFAULT NULL,
  `create_user_id` bigint(20) DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  `user_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=328 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `domain_result` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) DEFAULT NULL,
  `company_type` varchar(255) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `domain` varchar(255) DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `icp_no` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8

3.修改字段支持区分大小写 
 设置数据库排序规则支持区分大小写(视情况可选)
 修改表具体字段
 ALTER TABLE domain_result MODIFY COLUMN domain VARCHAR(255) BINARY;

roles以逗号分隔, 查询包含role值为1的列表

select * from user where  find_in_set('1', roles);

显示user的角色名称

#方式1

select
	user.*,
	group_concat( role.name) as role_names 
from
	user as user,
	role as role 
where
	find_in_set( role.id, user.roles) > 0 

#方式2	

select
	user.*,
	group_concat( role.name) as role_names 
from
	user as user
left join	
	role as role
on
	find_in_set( role.id, user.roles) > 0 	 
group by user.id

查询某类最新记录 (e.g: 房间当前在线人数)

## table: room_online_log 
## 字段: id  room_id  online_num  date_time


SELECT  
	a.room_id, 
	a.online_num, 
	a.date_time
FROM 
	room_online_log AS a 
INNER JOIN ( 
	## 获取room_id及最新时间
	SELECT
		room_id, 
		max(date_time) as date_time
  	FROM 
		room_online_log
	WHERE 
		room_id IN ('1', '2') 
		#AND online_time>= str_to_date('2019-07-09', '%Y-%m-%d')
		#AND online_time<str_to_date('2019-07-10', '%Y-%m-%d') 
	GROUP BY room_id
) as b
ON 
	a.room_id = b.room_id
AND 
	a.date_time = b.date_time

查询重复数据及删除重复数据(保留一条)

#查询重复的数据的个数 (user_id apply_key)
SELECT user_id, apply_key, COUNT(1) AS num FROM tb_machine 
WHERE deleted = 0
GROUP BY user_id, apply_key
HAVING num > 1 


#查询重复的数据 (user_id apply_key)
#SELECT * FROM tb_machine as tb_machine
SELECT tb_machine.id, tb_machine.user_id, tb_machine.apply_key, tb_machine.deleted, tb_machine.create_date 
FROM tb_machine as tb_machine
RIGHT JOIN 
###右连接(存在数据时返回,个数大于1时)###
(SELECT user_id, apply_key FROM tb_machine 
WHERE deleted = 0
GROUP BY user_id, apply_key
HAVING COUNT(1)  > 1 ) AS temp

ON tb_machine.user_id = temp.user_id
AND tb_machine.apply_key = temp.apply_key
AND tb_machine.deleted = temp.deleted

ORDER BY tb_machine.user_id, tb_machine.apply_key


#查询重复的数据中最大的id
SELECT max(id) as id FROM tb_machine 
WHERE deleted = 0
GROUP BY user_id, apply_key
HAVING COUNT(1) > 1 

#删除重复的数据 (user_id apply_key) 只保留对应的一条 (保留最大id)

###删除数据id在全部重复数据中的且保留重复数据中id最大的###

DELETE FROM tb_machine
WHERE id IN (
    SELECT id FROM (
        SELECT tb_machine.id 
        FROM tb_machine as tb_machine
        RIGHT JOIN 
    
        (SELECT user_id, apply_key FROM tb_machine 
        WHERE deleted = 0
        GROUP BY user_id, apply_key
        HAVING COUNT(1)  > 1 ) AS temp
      
        ON tb_machine.user_id = temp.user_id
        AND tb_machine.apply_key = temp.apply_key
        AND tb_machine.deleted = temp.deleted
        
        ORDER BY tb_machine.user_id, tb_machine.apply_key
    ) AS a
) 
AND id NOT IN (
    SELECT id FROM (
        SELECT max(id) as id FROM tb_machine 
        WHERE deleted = 0
        GROUP BY user_id, apply_key
        HAVING COUNT(1) > 1 
    ) as b
)

随机数

SELECT  (RAND() * 2900)

##更新随机端口
UPDATE server set open_port =  (RAND() * 10000 + 10000) WHERE open_port is null

replace 替换字段内容

UPDATE table_name  SET field_name = replace (field_name, 'from_str', 'to_str') 
WHERE ...

##更新表文件储存路径
UPDATE tb_file 
SET filepath = REPLACE(filepath, 'C:\\upload', 'D:\\upload')
WHERE id = "ef345db10a8e432bbc1681c948911ff0"

更新用户认证时间(用户表新增认证时间同步认证表的创建时间)

UPDATE user as user
 
RIGHT JOIN user_account_auth as auth
ON auth.user_id=user.id 

SET user.auth_date=auth.cdate

WHERE user.auth_date is null

查询MySQL数据库所占用大小 原文链接

#查询MySQL数据库里面的所有数据库各自占用大小
SELECT
	TABLE_SCHEMA,
	concat( TRUNCATE ( sum( data_length ) / 1024 / 1024, 2 ), ' MB' ) AS data_size,
	concat( TRUNCATE ( sum( index_length ) / 1024 / 1024, 2 ), 'MB' ) AS index_size 
FROM
	information_schema.TABLES 
GROUP BY
	TABLE_SCHEMA 
ORDER BY
	data_size DESC;

#查询MySQL数据库里面的单个数据库占用大小 （ds0）
SELECT
	CONCAT( TRUNCATE ( SUM( data_length ) / 1024 / 1024, 2 ), 'MB' ) AS data_size,
	CONCAT( TRUNCATE ( SUM( max_data_length ) / 1024 / 1024, 2 ), 'MB' ) AS max_data_size,
	CONCAT( TRUNCATE ( SUM( data_free ) / 1024 / 1024, 2 ), 'MB' ) AS data_free,
	CONCAT( TRUNCATE ( SUM( index_length ) / 1024 / 1024, 2 ), 'MB' ) AS index_size 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'ds0';

#查询MySQL数据库里面的单个数据库所有表各自占用大小 （ds0）
SELECT
	TABLE_NAME,
	concat( TRUNCATE ( data_length / 1024 / 1024, 2 ), ' MB' ) AS data_size,
	concat( TRUNCATE ( index_length / 1024 / 1024, 2 ), ' MB' ) AS index_size 
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'ds0' 
GROUP BY
	TABLE_NAME 
ORDER BY
	data_length DESC;