MySQL常见操作

最新推荐文章于 2024-07-25 16:06:56 发布

知识的宝藏

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量867

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/TalorSwfit20111208/article/details/139378747

版权

MySQL字符串连接

在MySQL中，字符串连接可以使用CONCAT()函数或双竖线||操作符进行。下面是两种方法的示例：

使用CONCAT()函数：

CONCAT(',2001,', 'ABC')

使用双竖线||操作符：

',2001,' || 'ABC'

您可以根据自己的偏好选择其中一种方法来进行字符串连接。请在您的查询中使用适当的语法来确保字符串连接的正确性。

MySQL字符串分割

例如有字符串: ,1004,2003,

则:


```sql
    SUBSTRING_INDEX( SUBSTRING_INDEX( A.sub_channel, ',', 2 ), ',', - 1 ) AS sub2,-->1004
    SUBSTRING_INDEX( SUBSTRING_INDEX( A.sub_channel, ',', 3 ), ',', - 1 ) AS sub3,-->2003


## Mysql不同条件设置相同的值（使用子查询）


WHEN type=1 THEN payable_price WHEN type=2 THEN payable_price ELSE MAX(payable_price)

type=1 表示订单维度，type=2表示商品维度，需要无论type值为多少都取type=1时的payable_price值

解决方案：
使用SQL子查询;

```sql
SELECT  
    CASE   
        WHEN type = 1 THEN payable_price  
        WHEN type = 2 THEN (SELECT payable_price FROM your_table WHERE type = 1 LIMIT 1)  
        ELSE (SELECT MAX(payable_price) FROM your_table)  
    END AS amount  
FROM your_table;

SQL explain 显示子查询A类型为ALL怎么优化

当 SQL EXPLAIN 显示子查询 A 的类型为 ALL 时，这意味着数据库系统正在执行全表扫描，而不是使用索引来执行子查询。全表扫描可能会导致性能下降，特别是在大型表上。

为了优化这种情况，您可以考虑以下几点：

索引优化：
- 确保表 order_goods 和 orders 上存在适当的索引，以便优化查询性能。在子查询中使用的列（如 A.serial、A.order_id、A.goods_sku_id、A.remarks）应该有相应的索引。
- 索引可以帮助数据库系统更快地定位和检索数据，减少全表扫描的需要。
重写查询：
- 考虑重写查询以减少全表扫描的需求。您可以尝试优化查询逻辑，使其更有效率地利用索引。
- 可以尝试拆分复杂的查询为多个简单的查询，然后使用 JOIN 来组合结果。
优化查询条件：
- 确保查询条件能够充分利用索引。避免在 WHERE 子句中使用函数或操作符，这可能会导致索引失效。
- 尽量避免在 WHERE 子句中使用 LIKE '%pattern%' 这样的模糊搜索，因为这种模式匹配会导致索引无法有效使用。
统计信息更新：
- 确保数据库中的统计信息是最新的，这有助于优化查询执行计划。您可以通过更新表的统计信息来帮助优化查询性能。
分析执行计划：
- 通过分析执行计划（EXPLAIN）来了解数据库系统是如何执行查询的，以便进一步优化查询。

综上所述，优化全表扫描的方法包括索引优化、查询重写、优化查询条件、更新统计信息和分析执行计划。通过这些方法，您可以尝试改善查询性能并减少全表扫描的情况。

MySQL全部扫描分析

要查看为什么 MySQL 选择执行全表扫描的原因，你可以使用 EXPLAIN 命令来分析查询执行计划。以下是如何使用 EXPLAIN 命令的步骤：

在查询前添加 EXPLAIN：在你的查询语句之前添加关键字 EXPLAIN。
运行查询：运行带有 EXPLAIN 的查询语句。
查看执行计划：执行计划将显示有关 MySQL 如何执行查询的详细信息，包括访问类型（type）、使用的索引、扫描的行数等。

下面是一个示例查询和执行计划的步骤：

EXPLAIN
SELECT
    A.serial,
    GROUP_CONCAT(DISTINCT COALESCE(A.imei, B.imei)) AS imei_list
FROM
    oppo_cn.t_order_goods_service_bind A
JOIN oppo_cn.t_order_goods_imei_bind B ON A.serial = B.serial
GROUP BY serial
LIMIT 5000;

运行上述带有 EXPLAIN 的查询后，你将看到类似以下的输出，其中包含了关于查询执行计划的信息：

+----+-------------+-------+--------+------------------+---------+---------+---------------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys    | key     | key_len | ref                                   | rows | Extra       |
+----+-------------+-------+--------+------------------+---------+---------+---------------------------------------+------+-------------+
| 1  | SIMPLE      | A     | index  | PRIMARY          | PRIMARY | 4       | NULL                                  | 1000 | Using index |
| 1  | SIMPLE      | B     | eq_ref | PRIMARY,serial   | PRIMARY | 4       | oppo_cn.A.serial                      | 1    | Using index |
+----+-------------+-------+--------+------------------+---------+---------+---------------------------------------+------+-------------+

在这个输出中，你可以查看 type 列，它会告诉你 MySQL 访问表的方式。如果你看到 type 列的值为 ALL，那么 MySQL 正执行全表扫描。在 Extra 列中，可能会提供关于为什么 MySQL 选择执行全表扫描的额外信息。

通过分析 EXPLAIN 的输出，你可以了解为什么 MySQL 选择执行全表扫描，从而优化查询和索引以提高性能。

MySQL查询数据不一致

在您提供的两个查询中，您发现了查询结果的数据量不一致，一个查询结果为404条，另一个查询结果为410条。为了优化这两个查询并确保结果的一致性，您可以尝试以下几点：

统一字段命名：在两个查询中确保使用相同的字段命名，以便后续比较结果集时更容易对应。
检查 JOIN 条件：仔细检查 JOIN 条件，确保连接的字段是正确的，并且 JOIN 操作能够正确地匹配数据。
优化 WHERE 条件：在 WHERE 子句中，确保逻辑运算符的使用是符合预期的，尤其是 OR 和 AND 的组合使用。
使用合适的 JOIN 类型：根据数据之间的关系，选择合适的 JOIN 类型（如 INNER JOIN、LEFT JOIN 等）。
避免重复数据：确保在 JOIN 操作中不会因为重复数据而导致结果数量不一致。
检查 GROUP BY 条件：在 GROUP BY 子句中，确保正确地对结果进行分组。
性能优化：如果数据量较大，可以考虑对查询进行性能优化，例如添加合适的索引以提高查询效率。
逐步调试：可以逐步调试查询，逐步添加条件和 JOIN 操作，以便发现导致结果不一致的具体步骤。

MySQL timestamp日期格式转换为varchar日期格式

DATE_FORMAT(A.paid_at, '%Y-%m-%d %H:%i:%s')='0000-00-00 00:00:00'

MySQL unix时间戳和yyyy-mm-dd HH:mm:ss时间互转

MySQL yyyy-MM-dd HH:mm:ss格式转unix时间戳
转换为13位unix时间戳格式：UNIX_TIMESTAMP( created_at ) * 1000 AS created_at,

MySQL unix时间戳转换为 yyyy-MM-dd HH:mm:ss格式时间

FROM_UNIXTIME( A.create_time / 1000, '%Y-%m-%d %H:%i:%S' ) AS created_at

MySQL 字符串排序

在MySQL中，如果你有包含数字的字符串列，并且想要按照这些数字的大小进行排序，你可以使用CAST函数将字符串转换为数字，然后进行排序。以下是一个示例：

假设你有一个名为numbers的表，其中有一个名为num_str的字符串列，包含数字字符串，你可以按照这些数字的大小进行排序：

SELECT num_str
FROM numbers
ORDER BY CAST(num_str AS UNSIGNED);

在上面的示例中，CAST(num_str AS UNSIGNED)将num_str列中的字符串转换为无符号整数，然后按照这些整数进行排序。这样就可以实现按照字符串中数字的大小进行排序。

请注意，使用CAST函数时，要确保字符串列中的所有值都可以转换为数字，否则可能会导致错误。如果有不符合要求的数据，可能需要进行数据清洗或处理。

MySQL JSON NULL查询

最近遇到个奇葩问题，查询MySQL表A里的一个字段content，字段数据是json格式的，格式类似下面这种：


```javascript
{
	"errorCode": "SVCSTG.ALS.200.200",
	"errorMessage": "Report success.",
	"result": null
}


```sql
select  JSON_EXTRACT(content,'$.result') as a from A where a<> NULL ;

查询不出result字段为null的数据，然后使用json_type()函数看下字段的数据类型

select  JSON_TYPE(JSON_EXTRACT(content,'$.result')) as a from A where a<> NULL ;

查询结果显示确实是NULL，那就见鬼了！然后检查了字段是否包含空格，空串结果还是排除不掉null的数据然后使用

select  JSON_TYPE(JSON_EXTRACT(content,'$.result')) as a from A where a！= 'null'
and a!='' and a IS NOT NULL;

查询的结果还是有null数据，然后突发奇想查查result值为null的数据呢？

select  JSON_TYPE(JSON_EXTRACT(content,'$.result')) as a from A where a IS NULL ;

查询结果显示查询出的数据不包含result字段，因此可以断定JSON_TYPE中的NULL数据类型和MySQL中的NULL不是一回事，最后试了下<>'NULL’才凑效

select  JSON_TYPE(JSON_EXTRACT(content,'$.result')) as a from A where a<> 'NULL' ;

这个sql查询出的结果才是理想的数据~

结论：MySQL中的字段中json串中字段的null值等价于JSON_TYPE字符串"NULL"，坑爹啊

知识的宝藏

关注

24
点赞
踩
28

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL常见操作

查询结果显示查询出的数据不包含result字段，因此可以断定JSON_TYPE中的NULL数据类型和MySQL中的NULL不是一回事，最后试了下'NULL’才凑效。在您提供的两个查询中，您发现了查询结果的数据量不一致，一个查询结果为404条，另一个查询结果为410条。这样就可以实现按照字符串中数字的大小进行排序。：在 WHERE 子句中，确保逻辑运算符的使用是符合预期的，尤其是 OR 和 AND 的组合使用。在MySQL中，如果你有包含数字的字符串列，并且想要按照这些数字的大小进行排序，你可以使用。
复制链接

扫一扫