分类 mysql 下的文章

在MySQL中存储图片

2024-08-23

在MySQL中存储图片，通常是将图片文件转换为二进制数据进行存储。这可以通过将图片文件读取为字节流，然后将这个字节流存储到BLOB类型的字段中来完成。

以下是一个简单的例子，展示了如何使用Python和MySQL Connector库将图片存储到MySQL数据库中。

首先，确保你的MySQL数据库中有一个表，其中包含一个BLOB类型的字段用于存储图片数据。例如：




CREATE TABLE images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data LONGBLOB
);

然后，使用Python代码将图片存储到数据库中：




import mysql.connector
import os
 
# 连接到MySQL数据库
conn = mysql.connector.connect(
    host='hostname',
    user='username',
    password='password',
    database='database_name'
)
cursor = conn.cursor()
 
# 读取图片文件为二进制数据
with open('path_to_your_image.jpg', 'rb') as file:
    data = file.read()
 
# 插入数据到数据库
add_img_query = """
INSERT INTO images (data)
VALUES (%s)
"""
cursor.execute(add_img_query, (data,))
 
# 提交更改并关闭连接
conn.commit()
cursor.close()
conn.close()

请确保替换hostname, username, password, database_name和path_to_your_image.jpg为实际的数据库连接信息和图片路径。

从数据库中检索图片的过程则是将BLOB数据读取出来，写入到文件中：




import mysql.connector
import os
 
# 连接到MySQL数据库
conn = mysql.connector.connect(
    host='hostname',
    user='username',
    password='password',
    database='database_name'
)
cursor = conn.cursor()
 
# 查询数据库以获取图片数据
query = "SELECT data FROM images WHERE id = %s"
cursor.execute(query, (image_id,))  # 替换image_id为你想要检索的图片ID
 
# 获取结果
row = cursor.fetchone()
 
# 如果找到图片，将其写入文件
if row:
    data = row[0]
    with open('output_image.jpg', 'wb') as file:
        file.write(data)
 
# 关闭连接
cursor.close()
conn.close()

在这个例子中，我们首先连接到MySQL数据库，然后读取图片文件并将其作为二进制数据存储。要检索图片，我们执行一个查询以获取BLOB数据，然后将其写入到一个新的文件中。记得替换hostname, username, password, database_name和image_id为实际的数据库连接信息和图片ID。

2024-08-23




DELIMITER //
 
CREATE PROCEDURE `sp_test_adv`(
    IN p_in INT,
    OUT p_out INT,
    INOUT p_inout INT
)
BEGIN
    DECLARE v_var INT DEFAULT 0;
 
    IF p_in IS NOT NULL THEN
        SET v_var = p_in;
    ELSE
        SET v_var = -1;
    END IF;
 
    SET p_out = v_var * 2;
    SET p_inout = p_inout * 3;
 
    CASE p_in
        WHEN 0 THEN
            SET v_var = 0;
        WHEN 1 THEN
            SET v_var = 10;
        ELSE
            SET v_var = 100;
    END CASE;
 
    SELECT v_var;
END //
 
DELIMITER ;

这段代码创建了一个名为sp_test_adv的MySQL存储过程，它接受三个参数：一个输入参数p_in，一个输出参数p_out，以及一个带入-带出参数p_inout。它使用了IF语句来检查输入参数是否为空，并根据结果设置局部变量v_var的值。接着，它使用CASE语句来根据输入参数的值设置v_var的不同值。最后，它通过SELECT语句返回v_var的值，并分别为输出参数p_out和带入-带出参数p_inout设置了新的值。

MySQL基础：查询MySQL中某个表的索引信息

2024-08-23

在MySQL中，您可以使用SHOW INDEX命令来查看表的索引信息。以下是一个查询特定表索引的示例SQL语句：




SHOW INDEX FROM your_table_name;

将your_table_name替换为您想要查看索引的表名。这条命令会列出表的所有索引以及它们的相关信息，如索引名、列名、类型等。

例如，如果您有一个名为employees的表，并想要查看其索引信息，您可以执行：




SHOW INDEX FROM employees;

这将输出employees表的索引详情。

2024-08-23

报错信息提示是在使用DataGrip进行MySQL数据库的连接测试时遇到了问题，具体是关于MySQL的大小写敏感性设置。

MySQL数据库有两种主要的大小写敏感性设置：

lower_case_table_names: 这个系统变量主要影响数据库表名的存储和比较。当设置为1时，表名存储在小写中，比较不区分大小写。
innodb_file_format: 这个变量影响InnoDB表的大小写敏感性，当设置为Barracuda时，表名和列名的存储是区分大小写的。

报错信息中的无版本可能意味着DataGrip无法获取到特定版本的MySQL信息，或者是MySQL服务的版本信息未知。

解决方法：

确认MySQL服务器的版本，并检查是否存在大小写敏感性的已知问题。
如果你确实需要区分大小写，但是DataGrip默认可能是将所有的标识符转换为小写，你可以在连接设置中修改这个行为：
- 打开DataGrip的连接设置。
- 查找到SQL 大小写相关的设置。
- 根据需要选择区分大小写或不区分大小写。
如果你使用的是MySQL 5.7.6或更高版本，并且想要关闭大小写敏感性，可以在MySQL服务器上设置innodb_file_format为Barracuda，并且设置innodb_file_per_table为ON。
如果你不能更改服务器设置，那么在DataGrip中将所有标识符（例如表名和列名）保持为创建时的大小写即可。

请注意，更改大小写敏感性设置可能会影响数据库的一致性，因此在进行更改之前应该进行充分的测试。

慎重升级到MySQL 8.0.38及更高版本

2024-08-23

在升级MySQL之前，请确保已经做好了充分的准备，包括备份数据、测试升级过程和监控系统。MySQL 8.0.38引入了一些重要的改进和安全性提升，但也可能会带来一些不兼容和新的风险。

备份数据：使用mysqldump或其他备份方法备份所有数据库。
测试升级：在测试环境中尝试升级过程。
监控系统：在升级过程中监控系统性能和应用程序的响应情况。
阅读官方文档：检查MySQL 8.0.38的发行说明和升级指南。
检查兼容性：确保应用程序或数据库驱动与新版本兼容。
等待补丁：如果你的应用程序依赖特定的MySQL行为，确保这些行为在新版本中仍然有效。

如果你决定升级，这里是一般的步骤：




# 1. 备份当前的MySQL数据库
mysqldump --all-databases --master-data > full_backup.sql
 
# 2. 停止MySQL服务
sudo systemctl stop mysqld
 
# 3. 安装MySQL 8.0.38或更高版本
# 这取决于你的操作系统和包管理器
# 对于Debian/Ubuntu可以使用
sudo apt-get update
sudo apt-get install mysql-server
 
# 4. 在升级过程中启动MySQL，并检查错误日志
sudo systemctl start mysqld
 
# 5. 升级后运行mysql_upgrade检查并升级系统表
sudo mysql_upgrade -p
 
# 6. 重新加载权限表
sudo mysqladmin flush-privileges
 
# 7. 重新启动MySQL服务
sudo systemctl restart mysqld

在执行任何升级操作之前，请确保已经对当前环境进行了充分的测试和评估，以确保一切都按预期工作。如果你在升级过程中遇到任何问题，应该参考MySQL的官方文档或者寻求社区的帮助。

MySQL篇：事务

2024-08-23

在MySQL中，事务是一种机制，用于确保数据的一致性、完整性和隔离性。事务通过将一系列操作封装成一个执行单元，以保证这些操作要么全部成功，要么全部不执行。

事务的基本属性（ACID）：

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的各个操作要么全部执行成功，要么全部执行失败回滚，这保证了一系列操作的一致性。
一致性（Consistency）：事务开始和结束时，数据库的完整性约束没有被破坏。这确保了数据的状态符合现实世界的逻辑。
隔离性（Isolation）：事务之间相互独立，不互相影响。这防止了并发执行的事务之间的数据冲突。
持久性（Durability）：事务一旦提交，其对数据库的改变就是永久性的，即使系统发生故障也不会丢失。

在MySQL中，可以使用以下语句管理事务：




-- 开启一个新事务
START TRANSACTION;
 
-- 执行一系列操作
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1 = value1 WHERE condition;
 
-- 如果所有操作都成功，提交事务
COMMIT;
 
-- 如果操作中任何一个失败，回滚所有操作
ROLLBACK;

事务的隔离级别：

MySQL 提供了不同的隔离级别来控制事务之间的可见性和并发性能：

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

设置隔离级别的示例：




-- 查看当前的隔离级别
SELECT @@SESSION.TX_ISOLATION;
 
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

在实际应用中，确保始终处理事务中的异常情况，比如使用 try-catch-finally 结构来保证在 PHP 中可以这样做：




try {
    // 开始事务
    mysqli_query($conn, "START TRANSACTION;");
 
    // 执行一系列操作
    mysqli_query($conn, "INSERT INTO table_name (column1, column2) VALUES (value1, value2);");
    mysqli_query($conn, "UPDATE table_name SET column1 = value1 WHERE condition;");
 
    // 提交事务
    mysqli_query($conn, "COMMIT;");
} catch (Exception $e) {
    // 发生错误，回滚事务
    mysqli_query($conn, "ROLLBACK;");
    // 处理异常，比如记录日志、通知用户等
}

确保在事务操作结束后，连接会话的隔离级别和其他设置保持不变，以免影响其他事务的执行。

MySQL 数据库表设计和优化

2024-08-23




-- 创建一个新的数据库，用于存储用户信息
CREATE DATABASE IF NOT EXISTS user_db;
USE user_db;
 
-- 创建用户信息表
CREATE TABLE IF NOT EXISTS user_info (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  signup_date DATE NOT NULL DEFAULT CURRENT_DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 添加用户信息
INSERT INTO user_info (username, email) VALUES ('张三', 'zhangsan@example.com');
INSERT INTO user_info (username, email) VALUES ('李四', 'lisi@example.com');
 
-- 查询用户信息
SELECT * FROM user_info;

这段代码展示了如何创建一个新的数据库和表，并插入一些示例用户信息。它还演示了如何使用AUTO_INCREMENT来自动生成主键，以及如何使用UNIQUE约束来确保电子邮件地址的唯一性。最后，它展示了如何查询这些用户信息。这是一个简单的数据库表设计和操作示例，适用于入门级的数据库使用。

2024-08-23

报错解释：

这个错误表明你尝试使用用户名 root 连接到 MySQL 数据库时，权限被拒绝了。这通常是因为以下几个原因：

用户名或密码不正确。
root 用户没有从当前位置访问数据库的权限。
root 用户权限未正确配置或已经被删除或修改。

解决方法：

确认你使用的用户名和密码是正确的。
如果你在远程服务器上，确保 root 用户有从远程连接的权限。可以使用如下命令授权：
```
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
```
其中 % 表示允许从任何IP地址连接，你可以替换为特定的IP以增加安全性。
如果你最近修改了用户权限，尝试刷新权限表，让更改生效：
```
FLUSH PRIVILEGES;
```
确保MySQL服务正在运行，并且你的连接字符串（包括端口号等）是正确的。

如果以上步骤不能解决问题，请检查 MySQL 的用户表，确认 root 用户的权限设置，并适当调整。

Linux使用宝塔面板安装MySQL结合内网穿透实现公网连接本地数据库

2024-08-23

要在Linux上使用宝塔面板安装MySQL并通过内网穿透实现公网连接到本地数据库，你需要执行以下步骤：

安装宝塔面板。
通过宝塔面板安装MySQL。
设置内网穿透，使MySQL服务可以从公网访问。

以下是示例步骤：

安装宝塔面板：




curl -O https://download.bt.cn/install/install_6.0.sh && sudo bash install_6.0.sh

通过宝塔面板安装MySQL：

登录宝塔面板后，在软件管理中安装MySQL。

设置内网穿透：

使用宝塔面板的内网穿透功能，开启内网穿透并获取公网地址。
或者使用其他内网穿透工具，如frp、ngrok等。

假设你已经有了内网穿透工具，以下是使用frp的示例步骤：

下载并安装frp：




wget https://github.com/fatedier/frp/releases/download/v0.37.1/frp_0.37.1_linux_amd64.tar.gz
tar -zxvf frp_0.37.1_linux_amd64.tar.gz

编辑frp的配置文件frps.ini，设置端口和认证信息：




[common]
bind_port = 7000

启动frp服务端：




./frps -c frps.ini

在客户端的frpc.ini配置文件中设置MySQL隧道：




[mysql]
type = tcp
local_ip = 127.0.0.1
local_port = 3306
remote_port = 6000

在服务端启动frpc客户端，连接到frp服务端：




./frpc -c frpc.ini

现在，你可以通过公网地址（由内网穿透服务提供）和frp配置中的remote_port连接到本地的MySQL服务。记得设置合适的防火墙规则以允许这些端口的流量通过。