Mysql

如何從單個列中提取證書屬性並拆分為多個？

  • October 24, 2020

在我的表中，我有一個cert_attr儲存Certificate X.509 Attributes的欄位（比如說） 。

這是 3 行的範例（每行對應一個欄位）：

"CN=User1, OU=Eng, O=Company Ltd, L=D4, S=Dublin, C=IE"
"CN=User2, OU=Eng, O=Company Ltd, L=D2, S=Dublin, C=IE"
"OU=Eng, O=Company Ltd"

我正在嘗試使用SELECT以下方式將欄位的值拆分為單獨的列：

SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "CN=", -1), ", ", 1) as CN,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "OU=", -1), ", ", 1) as OU,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "O=", -1), ", ", 1) as O,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "L=", -1), ", ", 1) as L,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "ST=", -1), ", ", 1) as ST,
SUBSTRING_INDEX(SUBSTRING_INDEX(cert_attr, "C=", -1), ", ", 1) as C
FROM mytable

這可行，但是缺少某些屬性的行存在問題。

因此，在欄位字元串中缺少屬性的情況下，我希望該列為空，但它會返回整個字元串。

前兩行範例按預期工作，正確返回以下列：

| CN    | OU  | O           | L  | S.     | C  |
| ----- | --- | ----------- | -- | ------ | -- |
| User1 | Eng | Company Ltd | D4 | Dublin | IE |
| User2 | Eng | Company Ltd | D2 | Dublin | IE |

問題在於第 3 行範例，我希望在找不到子字元串模式時返回一個空字元串：

| CN         | OU  | O           | L          | S.         | C          |
| ---------- | --- | ----------- | ---------- | ---------- | ---------- |
| OU=Eng,... | Eng | Company Ltd | OU=Eng,... | OU=Eng,... | OU=Eng,... |

而是返回整個字元串。

問題：

SUBSTRING_INDEX()當找不到子字元串時，有什麼方法可以返回一個空字元串？或者也許還有其他功能（如正則表達式）或其他解決方法？

我的目標是通過將這些屬性放在具有有效值的單獨列中來將數據提取到 TSV 文件中：

mysql mytable < query.sql > cert_attributes.tsv

第一部分只是向您展示該程序有效

CREATE tABLE tablecertificate  (
stationery_name varchar(500) 

INSERT INTO tablecertificate values 
("CN=User1, OU=Eng, O=Company Ltd, L=D4, S=Dublin, C=IE"),
("CN=User2, OU=Eng, O=Company Ltd, L=D2, S=Dublin, C=IE"),
("OU=Eng, O=Company Ltd")

CREATE PROCEDURE `splitcertifcate`()
BEGIN
 DECLARE current_pos INT DEFAULT 1;
 DECLARE delim CHAR DEFAULT ',';
 DECLARE current VARCHAR(100) DEFAULT '';
 DECLARE CN VARCHAR(100) DEFAULT '';
 DECLARE OU VARCHAR(100) DEFAULT '';
 DECLARE O VARCHAR(100) DEFAULT '';
 DECLARE L VARCHAR(100) DEFAULT '';
 DECLARE S VARCHAR(100) DEFAULT '';
 DECLARE C VARCHAR(100) DEFAULT '';
 DECLARE rest_cert_part VARCHAR(100) DEFAULT '';
 DECLARE current_cert_part  VARCHAR(100) DEFAULT '';
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE certificate varchar(500) DEFAULT "";
  DEClARE curcertificate 
      CURSOR FOR 
          SELECT stationery_name FROM tablecertificate;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
        
        
  #Temporary Table that holds the splittet part
   DROP TEMPORARY TABLE IF EXISTS mycertificate;
  CREATE TEMPORARY TABLE mycertificate(CN VARCHAR(100), OU VARCHAR(100), O VARCHAR(100) , L  VARCHAR(100), S VARCHAR(100), C VARCHAR(100));

  OPEN curcertificate;
    getcertificate: LOOP
      # get first row elemnt
      FETCH curcertificate INTO certificate;
      IF finished = 1 THEN 
          #Last element reached
          LEAVE getcertificate;
      END IF;
        SET CN = '';
        SET OU = '';
        SET O = '';
        SET L = '';
        SET S = '';
        SET C = '';
      SET current_pos =  LOCATE(delim,certificate);
        SET current_cert_part = SUBSTRING(certificate,1,current_pos-1);
      SET rest_cert_part = SUBSTRING(certificate from current_pos+1);
        IF length(trim(current_cert_part)) = 0   THEN
          SET current_cert_part = rest_cert_part;
      END IF;
      #Examine first element of the string
        CASE 
          WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
          WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
        END CASE;
        WHILE current_pos <> 0 DO
          #loop throuigh the string
          set  current_pos = LOCATE(delim,rest_cert_part);
          set current_cert_part = SUBSTRING(rest_cert_part,1,current_pos-1);
          set rest_cert_part = SUBSTRING(rest_cert_part from current_pos+1);

          if length(trim(current_cert_part)) = 0   then
              #ÖLast elemnt in string
              set current_cert_part = rest_cert_part;
          end if;    
          CASE 
              WHEN INSTR(current_cert_part, "CN=")  > 0 THEN SET CN = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "OU=")  > 0 THEN SET OU = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "O=")  > 0 THEN SET O = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "L=")  > 0 THEN SET L = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "S=")  > 0 THEN SET S = TRIM(current_cert_part);
              WHEN INSTR(current_cert_part, "C=")  > 0 THEN SET C = TRIM(current_cert_part);
          END CASE;
      END WHILE;
        #In sert splittet strung  row in temp table
       INSERT INTO mycertificate VALUES (  CN,OU,O,L,S,C);


  END LOOP getcertificate;
  CLOSE curcertificate;
SELECT * FROM mycertificate;
END

cALL splitcertifcate();

中文 | 歐 | ○ | 大號 | 小號 | C 
:------- | :----- | :------------ | :--- | :------- | :---
CN=使用者1 | 歐=英| O=有限公司| L=D4 | S=都柏林 | C=IE
CN=使用者2 | 歐=英| O=有限公司| L=D2 | S=都柏林 | C=IE
 | 歐=英| O=有限公司| | | 

✓

db<>在這裡擺弄

完整的儲存過程，包括生成 tsv 文件

DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `splitcertifcate`()
BEGIN
DECLARE current_pos INT DEFAULT 1;
DECLARE delim CHAR DEFAULT ',';
DECLARE current VARCHAR(100) DEFAULT '';
DECLARE CN VARCHAR(100) DEFAULT '';
DECLARE OU VARCHAR(100) DEFAULT '';
DECLARE O VARCHAR(100) DEFAULT '';
DECLARE L VARCHAR(100) DEFAULT '';
DECLARE S VARCHAR(100) DEFAULT '';
DECLARE C VARCHAR(100) DEFAULT '';
DECLARE rest_cert_part VARCHAR(100) DEFAULT '';
DECLARE current_cert_part  VARCHAR(100) DEFAULT '';
   DECLARE finished INTEGER DEFAULT 0;
   DECLARE certificate varchar(500) DEFAULT "";
   DEClARE curcertificate 
       CURSOR FOR 
           SELECT stationery_name FROM tablecertificate;

   -- declare NOT FOUND handler
   DECLARE CONTINUE HANDLER 
       FOR NOT FOUND SET finished = 1;
       
   #Temporary Table that holds the splittet part
    DROP TEMPORARY TABLE IF EXISTS mycertificate;
   CREATE TEMPORARY TABLE mycertificate(CN VARCHAR(100), OU VARCHAR(100), O VARCHAR(100) , L  VARCHAR(100), S VARCHAR(100), C VARCHAR(100));

   OPEN curcertificate;
   getcertificate: LOOP
       # get first row elemnt
       FETCH curcertificate INTO certificate;
       IF finished = 1 THEN 
           #Last element reached
           LEAVE getcertificate;
       END IF;
       SET CN = '';
       SET OU = '';
       SET O = '';
       SET L = '';
       SET S = '';
       SET C = '';
       SET current_pos =  LOCATE(delim,certificate);
       SET current_cert_part = SUBSTRING(certificate,1,current_pos-1);
       SET rest_cert_part = SUBSTRING(certificate from current_pos+1);
       IF length(trim(current_cert_part)) = 0   THEN
           SET current_cert_part = rest_cert_part;
       END IF;
       #Examine first element of the string
       CASE 
           WHEN INSTR(current_cert_part, "CN=")  &gt; 0 THEN SET CN = TRIM(current_cert_part);
           WHEN INSTR(current_cert_part, "OU=")  &gt; 0 THEN SET OU = TRIM(current_cert_part);
           WHEN INSTR(current_cert_part, "O=")  &gt; 0 THEN SET O = TRIM(current_cert_part);
           WHEN INSTR(current_cert_part, "L=")  &gt; 0 THEN SET L = TRIM(current_cert_part);
           WHEN INSTR(current_cert_part, "S=")  &gt; 0 THEN SET S = TRIM(current_cert_part);
           WHEN INSTR(current_cert_part, "C=")  &gt; 0 THEN SET C = TRIM(current_cert_part);
       END CASE;
       WHILE current_pos &lt;&gt; 0 DO
           #loop throuigh the string
           set  current_pos = LOCATE(delim,rest_cert_part);
           set current_cert_part = SUBSTRING(rest_cert_part,1,current_pos-1);
           set rest_cert_part = SUBSTRING(rest_cert_part from current_pos+1);

           if length(trim(current_cert_part)) = 0   then
               #ÖLast elemnt in string
               set current_cert_part = rest_cert_part;
           end if;    
           CASE 
               WHEN INSTR(current_cert_part, "CN=")  &gt; 0 THEN SET CN = TRIM(current_cert_part);
               WHEN INSTR(current_cert_part, "OU=")  &gt; 0 THEN SET OU = TRIM(current_cert_part);
               WHEN INSTR(current_cert_part, "O=")  &gt; 0 THEN SET O = TRIM(current_cert_part);
               WHEN INSTR(current_cert_part, "L=")  &gt; 0 THEN SET L = TRIM(current_cert_part);
               WHEN INSTR(current_cert_part, "S=")  &gt; 0 THEN SET S = TRIM(current_cert_part);
               WHEN INSTR(current_cert_part, "C=")  &gt; 0 THEN SET C = TRIM(current_cert_part);
           END CASE;
       END WHILE;
       #In sert splittet strung  row in temp table
      INSERT INTO mycertificate VALUES (  CN,OU,O,L,S,C);


   END LOOP getcertificate;
   CLOSE curcertificate;

   SELECT * FROM mycertificate INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/cert.tsv"
   FIELDS TERMINATED BY '\t'
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n';
END//
DELIMITER ;

結果 tsv 文件

"CN=User1"  "OU=Eng"    "O=Company Ltd" "L=D4"  "S=Dublin"  "C=IE"
"CN=User2"  "OU=Eng"    "O=Company Ltd" "L=D2"  "S=Dublin"  "C=IE"
""          "OU=Eng"    "O=Company Ltd"  ""       ""        ""

您必須檢查 INTO OUTFILE 文件夾，該文件夾必須與 my.ini/cnf 文件中的條目相對應

secure-file-priv='C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/'

引用自：https://dba.stackexchange.com/questions/278585

相關問答

Mysql

使用 SQL 重命名表中所有出現的 HTML 類名

  • June 16, 2021
檢視問答
Mysql

如何排除從 mysqldumpslow 命令返回的查詢

  • February 19, 2021
檢視問答
Mysql

使用原始值的子字元串批量更新列中的所有單元格

  • June 27, 2020
檢視問答
Mysql

如何在 MySQL 中正則表達式正前瞻

  • October 22, 2019
檢視問答
Mysql

如何使用 mysql 正則表達式子集語法選擇除“00”之外的任何幾個數字？

  • October 17, 2019
檢視問答
Mysql

如何用正則表達式查詢mysql表？

  • October 17, 2019
檢視問答