MySQL逗号分割字段的行列转换测试改进
p由于很多业务表因为历史原因或者性能原因，都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值（具体结构见下表）。/pp这种模式下，应用常常需要将这个列依据分隔符进行分割，并得到列转行的结果。/p span class=cnblogs_code_copy/spanp style
<p>由于很多业务表因为历史原因或者性能原因，都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值（具体结构见下表）。</p><p>这种模式下，应用常常需要将这个列依据分隔符进行分割，并得到列转行的结果。</p>
<span class="cnblogs_code_copy"></span><p style="margin: 10px auto; line-height: 19px; font-family: verdana, sans-serif; font-size: 13px;">表数据：</p>
| ID | Value |
| 1 | tiny,small,big |
| 2 | small,medium |
| 3 | tiny,big |
期望得到结果：
| ID | Value |
| 1 | tiny |
| 1 | small |
| 1 | big |
| 2 | small |
| 2 | medium |
| 3 | tiny |
| 3 | big |
<span style="line-height: 1.5;">#需要处理的表 </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">create</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">table</span> tbl_name (ID <span style="color: rgb(0, 0, 255); line-height: 1.5;">int</span> ,mSize <span style="color: rgb(0, 0, 255); line-height: 1.5;">varchar</span>(<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">100</span><span style="line-height: 1.5;">)); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> tbl_name <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">1</span>,<span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">tiny,small,big</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> tbl_name <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">2</span>,<span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">small,medium</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> tbl_name <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">3</span>,<span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">tiny,big</span><span style="color: rgb(255, 0, 0); line-height: 1.5;">'</span><span style="line-height: 1.5;">); #用于循环的自增表 </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">create</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">table</span> incre_table (AutoIncreID <span style="color: rgb(0, 0, 255); line-height: 1.5;">int</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> incre_table <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">1</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> incre_table <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">2</span><span style="line-height: 1.5;">); </span><span style="color: rgb(0, 0, 255); line-height: 1.5;">insert</span> <span style="color: rgb(0, 0, 255); line-height: 1.5;">into</span> incre_table <span style="color: rgb(0, 0, 255); line-height: 1.5;">values</span> (<span style="color: rgb(128, 0, 0); line-height: 1.5; font-weight: bold;">3</span>);
<span style="color:rgb(0,0,255); line-height:1.5">select</span> a.ID,substring_index(substring_index(a.mSize,<span style="color:rgb(255,0,0); line-height:1.5">'</span><span style="color:rgb(255,0,0); line-height:1.5">,</span><span style="color:rgb(255,0,0); line-height:1.5">'</span>,b.AutoIncreID),<span style="color:rgb(255,0,0); line-height:1.5">'</span><span style="color:rgb(255,0,0); line-height:1.5">,</span><span style="color:rgb(255,0,0); line-height:1.5">'</span>,<span style="color:rgb(128,128,128); line-height:1.5">-</span><span style="color:rgb(128,0,0); line-height:1.5; font-weight:bold">1</span><span style="line-height:1.5">) </span><span style="color:rgb(0,0,255); line-height:1.5">from</span><span style="line-height:1.5"> tbl_name a </span><span style="color:rgb(128,128,128); line-height:1.5">join</span><span style="line-height:1.5"> incre_table b </span><span style="color:rgb(0,0,255); line-height:1.5">on</span> b.AutoIncreID <span style="color:rgb(128,128,128); line-height:1.5"> (length(a.mSize) <span style="color:rgb(128,128,128); line-height:1.5">-</span> length(<span style="color:rgb(255,0,255); line-height:1.5">replace</span>(a.mSize,<span style="color:rgb(255,0,0); line-height:1.5">'</span><span style="color:rgb(255,0,0); line-height:1.5">,</span><span style="color:rgb(255,0,0); line-height:1.5">'</span>,<span style="color:rgb(255,0,0); line-height:1.5">''</span>))<span style="color:rgb(128,128,128); line-height:1.5">+</span><span style="color:rgb(128,0,0); line-height:1.5; font-weight:bold">1</span><span style="line-height:1.5">) </span><span style="color:rgb(0,0,255); line-height:1.5">order</span> <span style="color:rgb(0,0,255); line-height:1.5">by</span> a.ID;</span>
原理分析：
这个join最基本原理是笛卡尔积。通过这个方式来实现循环。
以下是具体问题分析：
length(a.Size) - length(replace(a.mSize,',',''))+1 表示了，按照逗号分割后，改列拥有的数值数量，下面简称n
<span style="color:#00ff">select</span> a.ID,substring_index(substring_index(a.mSize,<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,b.AutoIncreID),<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,<span style="color:#808080">-</span><span style="color:#8000; font-weight:bold">1</span><span style="color:#000000">) </span><span style="color:#00ff">from</span><span style="color:#000000"> tbl_name a </span><span style="color:#808080">join</span><span style="color:#000000"> incre_table b </span><span style="color:#00ff">on</span> b.AutoIncreID <span style="color:#808080"> (length(a.mSize) <span style="color:#808080">-</span> length(<span style="color:#ff0ff">replace</span>(a.mSize,<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,<span style="color:#ff00">''</span>))<span style="color:#808080">+</span><span style="color:#8000; font-weight:bold">1</span><span style="color:#000000">) </span><span style="color:#00ff">order</span> <span style="color:#00ff">by</span> a.ID;</span>
原理分析：
这个join最基本原理是笛卡尔积。通过这个方式来实现循环。
以下是具体问题分析：
length(a.Size) - length(replace(a.mSize,',',''))+1 表示了，按照逗号分割后，改列拥有的数值数量，下面简称n
join过程的伪代码：
根据ID进行循环
{
判断：i 是否
{
获取最靠近第 i 个逗号之前的数据， 即 substring_index(substring_index(a.mSize,',',b.ID),',',-1)
i = i +1
}
ID = ID +1
}
总结：
这种方法的缺点在于，我们需要一个拥有连续数列的独立表（这里是incre_table)。并且连续数列的最大值一定要大于符合分割的值的个数。
例如有一行的mSize 有100个逗号分割的值，那么我们的incre_table 就需要有至少100个连续行。
当然，mysql内部也有现成的连续数列表可用。如mysql.help_topic： help_topic_id 共有504个数值，一般能满足于大部分需求了。
改写后如下:
<span style="color:#00ff">select</span> a.ID,substring_index(substring_index(a.mSize,<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,b.help_topic_id<span style="color:#808080">+</span><span style="color:#8000; font-weight:bold">1</span>),<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,<span style="color:#808080">-</span><span style="color:#8000; font-weight:bold">1</span><span style="color:#000000">) </span><span style="color:#00ff">from</span><span style="color:#000000"> tbl_name a </span><span style="color:#808080">join</span><span style="color:#000000"> mysql.help_topic b </span><span style="color:#00ff">on</span> b.help_topic_id <span style="color:#808080"> (length(a.mSize) <span style="color:#808080">-</span> length(<span style="color:#ff0ff">replace</span>(a.mSize,<span style="color:#ff00">'</span><span style="color:#ff00">,</span><span style="color:#ff00">'</span>,<span style="color:#ff00">''</span>))<span style="color:#808080">+</span><span style="color:#8000; font-weight:bold">1</span><span style="color:#000000">) </span><span style="color:#00ff">order</span> <span style="color:#00ff">by</span> a.ID;</span>
测试实例：
-- SELECT help_topic_id FROM mysql.help_topic -- eg.把一个字段用“，”分隔开组合 select group_concat(user_id ORDER BY user_id ASC) as nids from admin_user SELECT b.did,GROUP_CONCAT(b.sid ORDER BY adjustment DESC,similar DESC) FROM test b GROUP BY b.did -- 1.如果多个导购同１张单的先分解 -- 加时间段 select a.DJBH,a.je,substring_index(substring_index(a.dgy_list_id,',',b.help_topic_id+1),',',-1) from ipos_qtlsd a join mysql.help_topic b on b.help_topic_id < (length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1) and a.djbh='BP0102_qtsy000070' order by a.DJBH; -- 2.取平均值 -- SELECT help_topic_id FROM mysql.help_topic -- 1.如果多个导购同１张单的先分解 -- @zddm -- @ rq select a.DJBH, substring_index(substring_index(a.dgy_list_id,',',b.help_topic_id+1),',',-1) AS FJID, substring_index(substring_index(a.dgy_list_mc,',',b.help_topic_id+1),',',-1) AS FJMC, FORMAT(a.je/(length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1),2) AS FJJE, je from ipos_qtlsd a join mysql.help_topic b on b.help_topic_id < (length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1) and a.rq BETWEEN UNIX_TIMESTAMP('2016-04-01') and UNIX_TIMESTAMP('2016-05-01') and a.djbh='gd_151125000001' order by a.DJBH; -- gd_151125000001 --3.分解后的指标 -- SELECT help_topic_id FROM mysql.help_topic -- 1.如果多个导购同１张单的先分解 -- @khdm_change 终端代码 -- @start_time 开始时间 -- @end_time 结束时间 -- SELECT * FROM ipos_qtlsd WHERE djbh='gd_151125000001' set @khdm_change ='BP0102'; set @start_time=UNIX_TIMESTAMP('2016-04-01'); set @end_time=UNIX_TIMESTAMP('2016-05-01'); SELECT FJID,FJMC,SUM(FJJE) FROM( select a.zddm,a.zdmc,a.DJBH, substring_index(substring_index(a.dgy_list_id,',',b.help_topic_id+1),',',-1) AS FJID, substring_index(substring_index(a.dgy_list_mc,',',b.help_topic_id+1),',',-1) AS FJMC, FORMAT(a.je/(length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1),2) AS FJJE, je from ipos_qtlsd a join mysql.help_topic b on b.help_topic_id < (length(a.dgy_list_id) - length(replace(a.dgy_list_id,',',''))+1) and a.rq BETWEEN @start_time and @end_time and a.zd_id=(SELECT id from com_base_kehu where khdm=@khdm_change) ) AA GROUP BY FJID,FJMC -- and a.djbh='gd_151125000001' -- order by a.DJBH;
Heiße KI -Werkzeuge
Undresser.AI Undress
KI-gestützte App zum Erstellen realistischer Aktfotos
AI Clothes Remover
Online-KI-Tool zum Entfernen von Kleidung aus Fotos.
Undress AI Tool
Ausziehbilder kostenlos
Clothoff.io
KI-Kleiderentferner
AI Hentai Generator
Erstellen Sie kostenlos Ai Hentai.
Heißer Artikel
Heiße Werkzeuge
Notepad++7.3.1
Einfach zu bedienender und kostenloser Code-Editor
SublimeText3 chinesische Version
Chinesische Version, sehr einfach zu bedienen
Senden Sie Studio 13.0.1
Leistungsstarke integrierte PHP-Entwicklungsumgebung
Dreamweaver CS6
Visuelle Webentwicklungstools
SublimeText3 Mac-Version
Codebearbeitungssoftware auf Gottesniveau (SublimeText3)
Heiße Themen
Wie verwende ich MySQL-Backup und -Wiederherstellung in PHP?
Jun 03, 2024 pm 12:19 PM
Das Sichern und Wiederherstellen einer MySQL-Datenbank in PHP kann durch Befolgen dieser Schritte erreicht werden: Sichern Sie die Datenbank: Verwenden Sie den Befehl mysqldump, um die Datenbank in eine SQL-Datei zu sichern. Datenbank wiederherstellen: Verwenden Sie den Befehl mysql, um die Datenbank aus SQL-Dateien wiederherzustellen.
Wie optimiert man die MySQL-Abfrageleistung in PHP?
Jun 03, 2024 pm 08:11 PM
Die MySQL-Abfrageleistung kann durch die Erstellung von Indizes optimiert werden, die die Suchzeit von linearer Komplexität auf logarithmische Komplexität reduzieren. Verwenden Sie PreparedStatements, um SQL-Injection zu verhindern und die Abfrageleistung zu verbessern. Begrenzen Sie die Abfrageergebnisse und reduzieren Sie die vom Server verarbeitete Datenmenge. Optimieren Sie Join-Abfragen, einschließlich der Verwendung geeigneter Join-Typen, der Erstellung von Indizes und der Berücksichtigung der Verwendung von Unterabfragen. Analysieren Sie Abfragen, um Engpässe zu identifizieren. Verwenden Sie Caching, um die Datenbanklast zu reduzieren. Optimieren Sie den PHP-Code, um den Overhead zu minimieren.
Wie füge ich mit PHP Daten in eine MySQL-Tabelle ein?
Jun 02, 2024 pm 02:26 PM
Wie füge ich Daten in eine MySQL-Tabelle ein? Mit der Datenbank verbinden: Stellen Sie mit mysqli eine Verbindung zur Datenbank her. Bereiten Sie die SQL-Abfrage vor: Schreiben Sie eine INSERT-Anweisung, um die einzufügenden Spalten und Werte anzugeben. Abfrage ausführen: Verwenden Sie die Methode query(), um die Einfügungsabfrage auszuführen. Bei Erfolg wird eine Bestätigungsmeldung ausgegeben.
Wie erstelle ich eine MySQL-Tabelle mit PHP?
Jun 04, 2024 pm 01:57 PM
Das Erstellen einer MySQL-Tabelle mit PHP erfordert die folgenden Schritte: Stellen Sie eine Verbindung zur Datenbank her. Erstellen Sie die Datenbank, falls sie nicht vorhanden ist. Wählen Sie eine Datenbank aus. Tabelle erstellen. Führen Sie die Abfrage aus. Schließen Sie die Verbindung.
Wie verwende ich gespeicherte MySQL-Prozeduren in PHP?
Jun 02, 2024 pm 02:13 PM
So verwenden Sie gespeicherte MySQL-Prozeduren in PHP: Verwenden Sie PDO oder die MySQLi-Erweiterung, um eine Verbindung zu einer MySQL-Datenbank herzustellen. Bereiten Sie die Anweisung zum Aufrufen der gespeicherten Prozedur vor. Führen Sie die gespeicherte Prozedur aus. Verarbeiten Sie die Ergebnismenge (wenn die gespeicherte Prozedur Ergebnisse zurückgibt). Schließen Sie die Datenbankverbindung.
So beheben Sie den Fehler „mysql_native_password nicht geladen' unter MySQL 8.4
Dec 09, 2024 am 11:42 AM
Eine der wichtigsten Änderungen, die in MySQL 8.4 (der neuesten LTS-Version von 2024) eingeführt wurden, besteht darin, dass das Plugin „MySQL Native Password“ nicht mehr standardmäßig aktiviert ist. Darüber hinaus entfernt MySQL 9.0 dieses Plugin vollständig. Diese Änderung betrifft PHP und andere Apps
Der Unterschied zwischen Oracle-Datenbank und MySQL
May 10, 2024 am 01:54 AM
Oracle-Datenbank und MySQL sind beide Datenbanken, die auf dem relationalen Modell basieren, aber Oracle ist in Bezug auf Kompatibilität, Skalierbarkeit, Datentypen und Sicherheit überlegen, während MySQL auf Geschwindigkeit und Flexibilität setzt und eher für kleine bis mittlere Datensätze geeignet ist. ① Oracle bietet eine breite Palette von Datentypen, ② bietet erweiterte Sicherheitsfunktionen, ③ ist für Anwendungen auf Unternehmensebene geeignet; ① MySQL unterstützt NoSQL-Datentypen, ② verfügt über weniger Sicherheitsmaßnahmen und ③ ist für kleine bis mittlere Anwendungen geeignet.
Wie lösche ich Daten aus einer MySQL-Tabelle mit PHP?
Jun 05, 2024 pm 12:40 PM
PHP bietet die folgenden Methoden zum Löschen von Daten in MySQL-Tabellen: DELETE-Anweisung: Wird zum Löschen von Zeilen, die Bedingungen entsprechen, aus der Tabelle verwendet. TRUNCATETABLE-Anweisung: Wird zum Löschen aller Daten in der Tabelle verwendet, einschließlich automatisch inkrementierter IDs. Praxisfall: Sie können Benutzer mithilfe von HTML-Formularen und PHP-Code aus der Datenbank löschen. Das Formular übermittelt die Benutzer-ID und der PHP-Code verwendet die DELETE-Anweisung, um den Datensatz, der der ID entspricht, aus der Benutzertabelle zu löschen.
