如何将MySQL查询结果插入同库STORY_TAGS表或适配Classic ASP

一、将查询结果插入到STORY_TAGS表 #

直接使用INSERT ... SELECT语法实现，可避免重复插入空标签或冗余数据：

INSERT INTO STORY_TAGS (TAG_, STORY_ID) -- 若STORY_TAGS无关联故事ID字段，可删除STORY_ID相关内容
SELECT DISTINCT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(s.TAGS_,',',mCtr),',',-1) AS valsAsRows,
  s.STORY_ID -- 替换为STORIES表的实际主键字段名
FROM (
  SELECT (cb.ctr*cc.ctr)+ca.ctr AS mCtr 
  FROM (SELECT 1 ctr UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ca 
  CROSS JOIN (SELECT 0 ctr UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) cb 
  CROSS JOIN (SELECT 0 ctr UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) cc 
  GROUP BY mCtr
) mc 
CROSS JOIN STORIES s
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(s.TAGS_,',',mCtr),',',-1) != '' -- 过滤空标签
GROUP BY valsAsRows, s.STORY_ID;

二、在Classic ASP页面中运行该查询 #

通过ADODB组件连接MySQL并执行查询，示例代码如下：

<%
' 配置数据库连接参数，根据实际环境修改
Dim connStr, conn, rs, sql
connStr = "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=你的数据库地址;DATABASE=你的库名;UID=用户名;PWD=密码;"

' 建立数据库连接
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr

' 拼接查询SQL语句
sql = "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(TAGS_,',',mCtr),',',-1) AS valsAsRows " & _
      "FROM (SELECT (cb.ctr*cc.ctr)+ca.ctr AS mCtr " & _
      "FROM (SELECT 1 ctr UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ca " & _
      "CROSS JOIN (SELECT 0 ctr UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) cb " & _
      "CROSS JOIN (SELECT 0 ctr UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) cc " & _
      "GROUP BY mCtr) mc " & _
      "CROSS JOIN STORIES " & _
      "GROUP BY valsAsRows;"

' 执行查询并处理结果
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn

' 遍历输出查询结果，可根据需求调整逻辑
Do While Not rs.EOF
    Response.Write("标签：" & rs("valsAsRows") & "<br>")
    rs.MoveNext
Loop

' 释放资源
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>

如果需要在ASP中直接执行插入操作，只需将上述sql变量替换为第一部分的INSERT语句，再用conn.Execute(sql)执行即可，无需使用Recordset。

内容的提问来源于stack exchange，提问作者Penguin