数据库-MySQL篇

MySQL数据库 🖥 📊🖥 #

db

安装 #

docker #

podman run -d --name mysql-container -e MYSQL_ROOT_PASSWORD={my-secret-pw} -e MYSQL_TIME_ZONE=UTC  -e MYSQL_CHARSET=utf8 -p 3306:3306 mysql:5.7

使用 #

常用命令 #

CREATE DATABASE {database_name} DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

按天统计 #

SELECT 
    DATE(created_at) AS date,
    COUNT(*) AS count
FROM 
    `TABLE_NAME`
WHERE 
    created_at >= "2025-01-01" AND created_at < "2025-02-01"
GROUP BY 
    DATE(created_at)
ORDER BY 
    date LIMIT 100

连接 #

使用MySQL命令行工具连接MySQL时，你可以使用以下命令来：

启用压缩传输 #

mysql --compress -u username -p

完整命令如：

mysql --compress -u{username} -p{passwd} --host {host}  {db name}

2024-05-27: 但是很可惜，很多第三方连接库都不支持这个参数。

MySQL 8.0 #

因为在更新8.0 之后更改了用户密码加密形式所以在使用客户端连接的时候会出现错误，所以需要

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' 
    PASSWORD EXPIRE NEVER; #修改加密规则  
ALTER USER 'root'@'localhost' IDENTIFIED WITH 
    mysql_native_password BY 'password';  #更新用户的密码  
FLUSH PRIVILEGES; #刷新权限

经此步骤，就可以将密码加密形式改为图形化客户端支持的加密形式，也或者更待客户端跟着服务端同步更新。

另外，配置之初，windows 中配置步骤如下：

  1. 添加系统环境变量path 为MySQL/bin
  2. 初始化 mysqld --initialize --user=mysql --console
  3. 根据初始化随机生成密码登陆
  4. 修改密码 emm 这里说的是在外部，当然也可以在修改加密形式的时候一并修改 😄 mysqladmin -u root -p password
  5. 添加系统服务 mysqld -install
  6. 启动服务 net start mysql

另外在图像化里面注意密码格式呦，还有使用navicat的话需要最新版本，否则无法设置加密格式，还得用命令行去修改……果然还是命令行永远最好用

更新 #

MySQL的update在被更新数值和更新传入数值相同时，执行速度相当的快，据说是mysql并不会执行更新动作但是它晓得更新动作的发生

删除 #

删除重复数据 #

零:

# 删除有重复的数据, 保留最大ID
SELECT id FROM playstore_pkg_info
WHERE `id` NOT IN (
    SELECT MAX(id)
    FROM playstore_pkg_info
    GROUP BY `packageName`
    HAVING COUNT(id) > 1
)
AND `packageName` IN (
    SELECT `packageName`
    FROM playstore_pkg_info
    GROUP BY `packageName`
    HAVING COUNT(id) > 1
) LIMIT 200

一:

    delete from alarm_calendar where id not in (SELECT maxid from (SELECT MAX(id) as maxid, 
    CONCAT(user_id,time,generic_name) as nameAndCode from alarm_calendar GROUP BY nameAndCode) t);

根据user_id, time, generic_name来打包重复，将重复数据删掉, 留下max，在自增里面即：最新数据

**二: **

SELECT pid, status FROM offer WHERE pid IN (SELECT pid FROM offer GROUP BY pid HAVING COUNT(id) > 1) ORDER BY `pid` asc LIMIT 100

MySQL 中文乱码 #

需要在连接时就指定✱charset✲ 而不该一味纠结于在程序在存储时的数据准备 此处使用的是

charset='gb2312'

另外，干脆就在数据库中设置存为 blob 格式

连接远程 win 数据库 #

开启某端口的示例：

添加防火墙例外端口

## 导出表记录
[^_^]
    2021-04-22 看了上面,深表惭愧...
```bash
mysql -uuser -ppasswd -hhost -e "use db_name; select * from table_name where chat_id in (...);" >> hello2.csv

在bash中直接执行sql语句，重定向保存

好处 #

可以规避 into outfile 的权限问题，导出的东西用csv也是可以看的，分隔符得以保留，csv可以正常阅览

注意点 #

只不过有,时分隔符可能有些问题，全文替换了就好

入站规则设置 #

第一步 选择 入站规则 然后 新建规则，选择 端口，然后下一步  第二步 选择TCP 选择特定端口 然后输入端口，如有多个端口需要用逗号隔开了 例如: 3306 第三步 选择允许连接 第四步 选择配置文件  第五步 输入规则名称 mysqlport

出站规则设置 #
  1. 第一步 选择出站规则 然后 新建规则，选择 端口，然后下一步 
  2. 第二步 选择TCP 选择特定端口 然后输入端口，如有多个端口需要用逗号隔开了 例如: 3306
  3. 第三步 选择允许连接
  4. 第四步 选择配置文件 
  5. 第五步 输入规则名称 mysqlport（或者无特殊要求下直接关闭防火墙）

字符串存储 #

1、总体来说： 　　1、char，存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限255。

　　2、varchar，存变长，速度慢，不存在空间浪费，不处理尾部空格，上限65535，但是有存储长度实际65532最大可用。

　　3、text，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限65535，会用额外空间存放数据长度，顾可以全部使用65535。

2、应用场景的问题： 　　当 varchar(n) 后面的 n 非常大的时候,我们是使用 varchar 好，还是 text 好呢？ 　　这是个明显的量变引发质变的问题。我们从2个方面考虑，第一是空间，第二是性能。

（1）首先从空间方面 从官方文档中我们可以得知当varchar大于某些数值的时候，其会自动转换为text，大概规则如下：

  • 大于varchar（255）变为 tinytext
  • 大于varchar（500）变为 text
  • 大于varchar（20000）变为 mediumtext 　　所以对于过大的内容使用varchar和text没有太多区别。 （2）从性能方面 　　索引会是影响性能的最关键因素，而对于text来说，只能添加前缀索引，并且前缀索引最大只能达到1000字节。 　　而貌似varhcar可以添加全部索引，但是经过测试，其实也不是。由于会进行内部的转换，所以long varchar其实也只能添加1000字节的索引，如果超长了会自动截断。

　我们认为当超过255的长度之后，使用varchar和text没有本质区别，只需要考虑一下两个类型的特性即可。（主要考虑text没有默认值的问题）

数据迁移 #

因为已有从数据库中读取数据进行返回的函数，所以没有必要再去写读取数据库的步骤（也没法写，因为数据库设计混乱，分不清哪儿是哪儿只有通过其对外函数才能取对相应数据） 但对外其是将一段时间内数据全部读取并且打包为json返回，所以一次性的话将是极大的一个文件，所以解决方法有：

  • 阅读其中源码，返回相应数据库指针，以此取出一个迁移一个
  • 编写分段读取函数，保证每次取出的数据不多，一段一段的迁移

反正我是采用一段一段了，毕竟源码太… 另外，一段一段的话打好日志，出现问题后可以分段纠正，也能间歇运行，另外下载好再迁也保证了数据emm，考虑如上

简单总结 #

  • 数据迁移程序
  • 数据检验补差程序
  • 断点运行机制
# 初始化
1. 读取配置文件（其中保存
* 数据迁移顺序——列表（内元素为字典key为* value为相应数据库、表名，
    时间起止，一次插入的时间跨度） ）

2. 根据移植顺序读取目标数据库中相应数据的最新数据日期（
* 若为空开始则开始插入）
* 删除此日期的所有数据，
* 根据一次插入的时间跨度以及终点生成时间区间
* 根据时间区间去移植数据

3. 当此类型数据移植完毕之后删除配置文件中列表的相应元素
# 执行动作
是

## 日志
将每一步的重要信息都打印~

常用命令 #

建立唯一约束 #

ALTER TABLE app_platforms
ADD CONSTRAINT unique_app_id_platform UNIQUE (app_id, platform)

删除重复数据 #

app_id, platform 重复

DELETE FROM app_platforms WHERE id in (SELECT id from ( SELECT MIN(id) as id, app_id, platform, COUNT(*) as count FROM app_platforms GROUP BY app_id, platform HAVING count > 1 ) as keep_ids)