常见用法环境配置 Debug MySQL

数据库

发布日期: 2022-01-03

文章字数: 2k

阅读时长: 9 分

阅读次数:

0 安装配置

0.1 MySQL+Navicat

详见博客：MySQL 安装（简单版，无可视化界面）
- 修改root用户临时密码：mysql 修改密码报错解决
详见博客：Windows上本地安装MySQL数据库

本机MYSQL服务名：MySQL80

root密码：XXXXXX

查看MySQL服务名，“管理工具” –> “服务”
破解版Navicat，详见博客：Navicat Premium v15 中文最新破解版（附：激活工具）

0.2 windows下MySQL my.ini文件位置

C:\ProgramData\MySQL\MySQL Server 8.0

0.3 centOS 7 安装MySQL

详见博客：CentOS 7.9 yum方式安装最新版本的MySQL 5.7
注意/etc/my.cnf配置文件里面必须包含[mysqld]

[mysqld]  # 必须包含
port=3306
socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
lower_case_table_names=1
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
innodb_file_per_table=1
skip_name_resolve=1
slow_query_log=1
slow_query_log_file=mysql-slow.log
symbolic-links=0
explicit_defaults_for_timestamp=1
server_id=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1
log_bin=mysql-bin
log_bin_index=mysql-bin.index
binlog_format=row

0.4 centOS 7 执行sql文件

详见博客：Centos 执行sql文件

0.5 JDBC数据库驱动的下载、安装与连接

详见博客：JDBC数据库驱动的下载、安装与连接

1 常见用法

1.1 workbench 执行语句

新建tab(new tab) ctrl+t
执行当前语句(execute current statement) ctrl+enter
执行全部或选中的语句(execute all or selection) ctrl+shift+enter
查看执行计划(explain current statement) ctrl+alt+x

1.2 text数据类型

TEXT可用于存储可以从1字节到4GB长度的文本字符串。
TINYTEXT - 1个字节(255个字符)
TEXT - 64KB(65,535个字符)
MEDIUMTEXT - 16MB(16,777,215个字符)
LONGTEXT - 4GB(4,294,967,295个字符)

1.3 命令行登录Mysql

详见博客：命令行登录Mysql，远程登录Mysql的方法

1.4 设置字段值取反操作

对布尔值取反，使用 ~。

update set status=~status where id=2; # status的值为true || false。

对0、1 数值取反，使用abs() 取绝对值。

update set status=abs(status-1) where id=1; # status的值为0 || 1。

这里如果数据表里的值一开始为0，那么会报一个错：

[Err] 1690 - BIGINT UNSIGNED value is out of range in ‘(resume.r_resume.status - 1)’

原因：取绝对值之前得到负值，看看是不是status勾选了unsigned，去掉再试试。

原文链接：https://www.cnblogs.com/xinxinmifan/p/11548935.html

1.5 时间比较

date(datetime) >= 2022-05-11  # 错误，2022-05-11会识别成数字
date(datetime) >= "2022-05-11"  # 正确，必须是字符串格式才能比较

1.6 查询语句中的字符串变量必须加引号

sql = f'select uid from {sql_table_name} where date="{date}" and period_type ="{period_type}"'  # 必须加引号

1.7 跨表查询，in

1.8 更新记录，update

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

1.9 修改用户密码

原文链接：MySQL修改root密码的4种方法

方法1：用SET PASSWORD命令
首先登录MySQL。
格式：mysql> set password for 用户名@localhost = password(‘新密码’);
例子：mysql> set password for root@localhost = password(‘123’);

方法2：用mysqladmin
格式：mysqladmin -u用户名 -p旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 123

方法3：用UPDATE直接编辑user表
首先登录MySQL。
mysql> use mysql;
mysql> update user set password=password(‘123’) where user=’root’ and host=’localhost’;
mysql> flush privileges;

2 Debug

2.1 登录时报错Access denied for user ‘root‘@‘localhost‘ (using password: YES)

详见博客：在MySQL登录时出现Access denied for user ‘root‘@‘localhost‘ (using password: YES) 拒绝访问，并可修改MySQL密码

2.2 发生系统错误5

详见博客：net stop mysql 发生系统错误 5。

2.3 报错 Duplicate foreign key constraint name ‘idXXXX’

因为在之前已经加过一个名称为idmanagetype外键索引了，如果重复则报错，在名称后面加一个1成功了。

2.4 Navicat连接远程主机数据库报错

详见博客：解决Navicat无法连接到MySQL的问题

2.5 连接 mysql 出现Client does not support authentication protocol requested by server

详见博客：navicat 连接 mysql 出现Client does not support authentication protocol requested by server解决方案

2.6 IDEA Database报红色下划线

详见博客：IDEA Database报红色下划线
注意使用Test conection，一般可以按照提示解决问题
修改了这里的文件，也要注意修改application.yml/application-dev.yml里关于数据库的设置，特别是password

2.7 MySql 执行 DELETE/UPDATE时，报 Error Code: 1175错误

详见博客：MySql 执行 DELETE/UPDATE时，报 Error Code: 1175错误

2.8 mysql 8.0+ 首次更改密码报错解决 You must reset your password using ALTER USER statement before

详见博客：mysql 8.0+ 首次更改密码报错解决 You must reset your password using ALTER USER statement before

2.9 mysql5.7设置简单密码报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

详见博客：mysql5.7设置简单密码报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

3 PyMySQL

3.1 下载安装数据库连接池DBUtils

DBUtils 是一套用于管理数据库连接池的包，为高频度高并发的数据库访问提供更好的性能，可以自动管理连接对象的创建和释放。最常用的两个外部接口是 PersistentDB 和 PooledDB，前者提供了单个线程专用的数据库连接池，后者则是进程内所有线程共享的数据库连接池。

详见博客：Python DBUtils安装之后 from DBUtils.PooledDB import PooledDB 报红

3.2 MySQL数据类型及占用空间

3.3 插入字典到数据库

placeholder = ','.join(['%s' for i in range(len(dic.values()))])
sql = f"insert into {table_name} ({','.join(dic.keys())}) values ({placeholder})"

3.4 多线程操作数据库-数据库连接池

该示例来自博客：python多线程操作数据库问题
重点在于每次对数据库的操作都要保持conn与cursor一致，否则会出现数据库访问错误等问题

import MySQLdb
from DBUtils.PooledDB import PooledDB
 
class MySQL:
 
	host = 'localhost'
	user = 'root'
	port = 3306
	pasword = ''
	db = 'testDB'
	charset = 'utf8'
 
	pool = None
	limit_count = 3 # 最低预启动数据库连接数量
 
	def __init__(self):
		self.pool = PooledDB(MySQLdb, self.limit_count, host = self.host, user = self.user, passwd = self.pasword, db = self.db,
			port = self.port, charset = self.charset, use_unicode = True)
 
	def select(self, sql):
		conn = self.pool.connection()
		cursor = conn.cursor()
		cursor.execute(sql)
		result = cursor.fetchall()
		cursor.close()
		conn.close()
		return result
 
	def insert(self, table, sql):
		conn = self.pool.connection()
		cursor = conn.cursor()
		try:
			cursor.execute(sql)
			conn.commit()
			return {'result':True, 'id':int(cursor.lastrowid)}
		except Exception as err:
			conn.rollback()
			return {'result':False, 'err':err}
		finally:
			cursor.close()
			conn.close()

本文尝试的包装的数据库连接池类

#-*- coding: utf-8 -*-
from DBUtils.PooledDB import PooledDB
import pymysql
from utils.config import mysql_info


class DB_POOL():
    __pool = None
    def __init__(self,db_name):
        self.__pool = PooledDB(pymysql, mincached=1, maxcached=20, host=mysql_info['host'], user=mysql_info['user'],
                          passwd=mysql_info['passwd'], db=db_name, port=3306, setsession=['SET AUTOCOMMIT = 1'],
                          cursorclass=pymysql.cursors.DictCursor, charset='utf8mb4')


    def to_connect(self):
        return self.__pool.connection()

    def is_connected(self, conn):
        """Check if the server is alive"""
        try:
            conn.ping(reconnect=True)
            # print("db is connecting")
        except:
            conn = self.to_connect()
            print("db reconnect")
        return conn

    def __getInsertId(self, cursor):
        cursor.execute('select @@IDENTITY AS id')
        result = cursor.fetchall()
        return result[0]['id']

    def __query(self, sql, param=None):
        conn = self.__pool.connection()
        cursor = conn.cursor()
        if param is None:
            count = cursor.execute(sql)
        else:
            count = cursor.execute(sql, param)
        return count, cursor

    def getAll(self, sql, param=None):
        # param查询条件值(元组\列表)
        # 返回list\boolean
        count, cursor = self.__query(sql, param)
        if count >= 1:
            res = cursor.fetchall()
        else:
            res = False
        return res

    def getOne(self, sql, param=None):
        count, cursor = self.__query(sql, param)
        if count >= 1:
            res = cursor.fetchone()
        else:
            res = False
        return res

    def getMany(self, sql, num, param=None):
        count, cursor = self.__query(sql, param)
        if count >= 1:
            res = cursor.fetchmany(num)
        else:
            res = False
        return res

    def insertOne(self, sql, value):
        conn = self.__pool.connection()
        cursor = conn.cursor()
        cursor.execute(sql, value)
        return self.__getInsertId(cursor)

    def update(self, sql, param=None):
        return self.__query(sql, param)[0]

    def insert_dict(self, dic, table_name):
        placeholder = ','.join(['%s' for i in range(len(dic.values()))])
        sql = f"insert into {table_name} ({','.join(dic.keys())}) values ({placeholder})"
        # print('sql', sql)
        return self.insertOne(sql, tuple(dic.values()))

db = DB_POOL('weibo')

if __name__ == '__main__':
    # 以下为测试代码
    db = DB_POOL('weibo')
    sql = 'select * from top100'
    res1 = db.getOne(sql)
    res2 = db.getAll(sql)
    res3 = db.getMany(sql, 100)
    res4 = db.insertOne("insert into top100(date, period_type, score, field_id, field_name, curr_rank, uid, screen_name) values (%s, %s,%s, %s, %s,%s,%s,%s)", ('20220418', 'month',96.4, 1001,'时尚美妆',2,3929704484,'美妆笔记Lucia'))
    res5 = db.update("update top100 set date='20220424' where uid=3929704484")

fdChen

https://blog.fdchen.host/2022/shu-ju-ku-mysql/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 fdChen !

常见用法环境配置 Debug MySQL

SprinBoot_Debug

SpringBoot项目中的常见bug与解决方案

2022-01-03 Java

Debug SpringBoot

Pandas

Python使用之Pandas教程，包含Pandas在数据分析及数据处理项目中的常见用法

2022-01-02 Python

教程 Pandas

MySQL使用

0 安装配置

0.1 MySQL+Navicat

0.2 windows下MySQL my.ini文件位置

0.3 centOS 7 安装MySQL

0.4 centOS 7 执行sql文件

0.5 JDBC数据库驱动的下载、安装与连接

1 常见用法

1.1 workbench 执行语句

1.2 text数据类型

1.3 命令行登录Mysql

1.4 设置字段值取反操作

1.5 时间比较

1.6 查询语句中的字符串变量必须加引号

1.7 跨表查询，in

1.8 更新记录，update

1.9 修改用户密码

2 Debug

2.1 登录时报错Access denied for user ‘root‘@‘localhost‘ (using password: YES)

2.2 发生系统错误5

2.3 报错 Duplicate foreign key constraint name ‘idXXXX’

2.4 Navicat连接远程主机数据库报错

2.5 连接 mysql 出现Client does not support authentication protocol requested by server

2.6 IDEA Database报红色下划线

2.7 MySql 执行 DELETE/UPDATE时，报 Error Code: 1175错误

2.8 mysql 8.0+ 首次更改密码报错解决 You must reset your password using ALTER USER statement before

2.9 mysql5.7设置简单密码报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

3 PyMySQL

3.1 下载安装数据库连接池DBUtils

3.2 MySQL数据类型及占用空间

3.3 插入字典到数据库

3.4 多线程操作数据库-数据库连接池

你的赏识是我前进的动力