MySQL常見操作

MySQL字符串連接

在MySQL中，字符串連接可以使用CONCAT()函數或雙豎線||操作符進行。下面是兩種方法的示例：

使用CONCAT()函數：

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

使用雙豎線||操作符：

',2001,' || 'ABC'

您可以根據自己的偏好選擇其中一種方法來進行字符串連接。請在您的查詢中使用適當的語法來確保字符串連接的正確性。

MySQL字符串分割

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

則:


```sqlSUBSTRING_INDEX( SUBSTRING_INDEX( A.sub_channel, ',', 2 ), ',', - 1 ) AS sub2,-->1004SUBSTRING_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
SELECTA.serial,GROUP_CONCAT(DISTINCT COALESCE(A.imei, B.imei)) AS imei_list
FROMoppo_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"，坑爹啊