本文共 6526 字，大约阅读时间需要 21 分钟。

mysql连接命令 (登录)

#本地登录/usr/local/mysql/bin/mysql -u root -pmysql -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为账户密码)

#开放所有ipGRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;FLUSH PRIVILEGES;##限定具体ipGRANT 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 usershow 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_binCREATE 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=utf83.修改字段支持区分大小写  设置数据库排序规则支持区分大小写(视情况可选) 修改表具体字段 ALTER TABLE domain_result MODIFY COLUMN domain VARCHAR(255) BINARY;

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

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

显示user的角色名称

#方式1select	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 userleft join		role as roleon	find_in_set( role.id, user.roles) > 0 	 group by user.id

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

## table: room_online_log ## 字段: id  room_id  online_num  date_timeSELECT  	a.room_id, 	a.online_num, 	a.date_timeFROM 	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

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

#查询重复的数据的个数 (user_id apply_key)SELECT user_id, apply_key, COUNT(1) AS num FROM tb_machine WHERE deleted = 0GROUP BY user_id, apply_keyHAVING num > 1 #查询重复的数据 (user_id apply_key)#SELECT * FROM tb_machine as tb_machineSELECT tb_machine.id, tb_machine.user_id, tb_machine.apply_key, tb_machine.deleted, tb_machine.create_date FROM tb_machine as tb_machineRIGHT JOIN ###右连接(存在数据时返回,个数大于1时)###(SELECT user_id, apply_key FROM tb_machine WHERE deleted = 0GROUP BY user_id, apply_keyHAVING COUNT(1)  > 1 ) AS tempON tb_machine.user_id = temp.user_idAND tb_machine.apply_key = temp.apply_keyAND tb_machine.deleted = temp.deletedORDER BY tb_machine.user_id, tb_machine.apply_key#查询重复的数据中最大的idSELECT max(id) as id FROM tb_machine WHERE deleted = 0GROUP BY user_id, apply_keyHAVING COUNT(1) > 1 #删除重复的数据 (user_id apply_key) 只保留对应的一条 (保留最大id)###删除数据id在全部重复数据中的且保留重复数据中id最大的###DELETE FROM tb_machineWHERE 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 authON auth.user_id=user.id SET user.auth_date=auth.cdateWHERE 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;

转载地址：https://blog.csdn.net/if_you_can_please_do/article/details/90694422 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！