☰

将插入转换为选择

tag-icon • tag-icon text-processing awk sed sql
将插入转换为选择

我有一个具有以下格式的文件

INSERT INTO table1(field1,field2,field3) VALUES('values1','value2','value3');
INSERT INTO table1(field1,field2,field3) VALUES('other_values1','other_value2','other_value3');
INSERT INTO table1(field1,field2,field3) VALUES('another_values1','another_value2','another_value3');
INSERT INTO table2(table2_field1,table2_field2,table2_field3,field4) VALUES('table2_values1','table2_value2','table2_value3');
INSERT INTO table2(table2_field1,table2_field2,table2_field3,table2_field4) VALUES('other_table2_values1','other_table2_value2','other_table2_value3');
INSERT INTO table2(table2_field1,table2_field2,table2_field3,table2_field4) VALUES('another_table2_values1','another_table2_value2','another_table2_value3','another_table2_value4');

我想要这个输出

SELECT * FROM table1 WHERE field1='values1' AND field2='values2' AND field3=='values3';
SELECT * FROM table1 WHERE field1='other_values1' AND field2='other_values2' AND field3=='other_values3';
SELECT * FROM table1 WHERE field1='another_values1' AND field2='another_values2' AND field3=='another_values3';
SELECT * FROM table2 WHERE table2_field1='table2_values1' AND table2_field2='table2_values2' AND table2_field3=='table2_values3' AND table2_field4=='table2_values4';
SELECT * FROM table2 WHERE table2_field1='table2_values1' AND table2_field2='table2_values2' AND table2_field3=='table2_values3' AND table2_field4=='table2_values4';
SELECT * FROM table2 WHERE table2_field1='table2_values1' AND table2_field2='table2_values2' AND table2_field3=='table2_values3' AND table2_field4=='table2_values4';

到目前为止我所做的是

cat test_inserts |awk -F '[()]' '{print $1 " WHERE "$2 $4}' |sed 's/INSERT INTO /SELECT * FROM /g'

它给了我以下输出

SELECT * FROM table1 WHERE field1,field2,field3'values1','value2','value3'
SELECT * FROM table1 WHERE field1,field2,field3'other_values1','other_value2','other_value3'
SELECT * FROM table1 WHERE field1,field2,field3'another_values1','another_value2','another_value3'
SELECT * FROM table2 WHERE table2_field1,table2_field2,table2_field3,field4'table2_values1','table2_value2','table2_value3'
SELECT * FROM table2 WHERE table2_field1,table2_field2,table2_field3,table2_field4'other_table2_values1','other_table2_value2','other_table2_value3'
SELECT * FROM table2 WHERE table2_field1,table2_field2,table2_field3,table2_field4'another_table2_values1','another_table2_value2','another_table2_value3','another_table2_value4'

答案1

复杂的AWK解决方案：

awk -F'[()]' '{ sub(/INSERT INTO */,"",$1); 
                printf "SELECT * FROM %s WHERE ",$1;
                len=split($2, f, ","); split($4, v, ","); 
                for (i=1; i<=len; i++) printf "%s=%s%s", f[i], v[i], (i==len? ";":" AND ");
                print "" 
              }' test_inserts
  • -F'[()]'- 复杂的字段分隔符
  • sub(/INSERT INTO */,"",$1)INSERT INTO-从第一个字段中删除短语（以提取桌子姓名）
  • printf "SELECT * FROM %s WHERE ",$1- 打印包含以下内容的 SQL 语句的开头桌子姓名
  • split($2, f, ",")- 用分隔符分割第二个字段,以获得字段名字（f成为字段名称数组）
  • split($4, v, ",")- 将第4个字段用分隔符分割,得到字段价值观（v成为字段值的数组）

输出：

SELECT * FROM table1 WHERE field1='values1' AND field2='value2' AND field3='value3';
SELECT * FROM table1 WHERE field1='other_values1' AND field2='other_value2' AND field3='other_value3';
SELECT * FROM table1 WHERE field1='another_values1' AND field2='another_value2' AND field3='another_value3';
SELECT * FROM table2 WHERE table2_field1='table2_values1' AND table2_field2='table2_value2' AND table2_field3='table2_value3' AND field4=;
SELECT * FROM table2 WHERE table2_field1='other_table2_values1' AND table2_field2='other_table2_value2' AND table2_field3='other_table2_value3' AND table2_field4=;
SELECT * FROM table2 WHERE table2_field1='another_table2_values1' AND table2_field2='another_table2_value2' AND table2_field3='another_table2_value3' AND table2_field4='another_table2_value4';

答案2

如果你喜欢这种事情，这里有一个 Python 的替代方案（比 更冗长但更易读awk，至少对我来说）：

#!/usr/bin/env python2
# -*- coding: ascii -*-
"""transform_query.py"""

import sys
import re

# Open the data file specified by the user
with open(sys.argv[1], 'r') as query_file:
    for row in query_file.readlines():

        # Regular expression to extract table name, field names, and values from each line
        match = re.search(
            r'^INSERT INTO '
                r'(?P<table>table\d+)\((?P<fields>[\w,]+)\) '
                r'VALUES\((?P<values>[^()]+)\);$',
            row.strip()
        )
        if match:

            # Store the table name (not necessary)
            table = match.group('table')

            # Split the fields string into a list
            fields = match.group('fields').split(',')

            # Split the values string into a list
            values = match.group('values').split(',')

            # Recombine the strings into a SELECT statement
            # and print the result
            print(
                "SELECT * FROM {} WHERE {};".format(
                    table,
                    ' AND '.join(
                        ['='.join([field, value]) for field, value in zip(fields, values)]
                    ),
                )
            )

运行：

python transform_query.py query.sql

这是输出：

SELECT * FROM table1 WHERE field1='values1' AND field2='value2' AND field3='value3';
SELECT * FROM table1 WHERE field1='other_values1' AND field2='other_value2' AND field3='other_value3';
SELECT * FROM table1 WHERE field1='another_values1' AND field2='another_value2' AND field3='another_value3';
SELECT * FROM table2 WHERE table2_field1='table2_values1' AND table2_field2='table2_value2' AND table2_field3='table2_value3';
SELECT * FROM table2 WHERE table2_field1='other_table2_values1' AND table2_field2='other_table2_value2' AND table2_field3='other_table2_value3';
SELECT * FROM table2 WHERE table2_field1='another_table2_values1' AND table2_field2='another_table2_value2' AND table2_field3='another_table2_value3' AND table2_field4='another_table2_value4';

答案3

如果您需要解释，请告诉我，我将在代码中添加注释。

要求：

  1. 字段和值的数量应该匹配。
  2. 字段名称和字段值不应包含以下字符：,)(;- 逗号、括号、分号。
gawk '{
    num = patsplit($3$4, arr, /[^,)(;]+/);
    num /= 2;

    printf("SELECT * FROM %s WHERE ", arr[1]);

    for(i = 2; i <= num; i++) {
        printf("%s=%s", arr[i], arr[num + i]);

        printf (i < num) ? " AND " : ";\n";
    }
}' input.txt

解释：

该字符串被选为示例：INSERT INTO table1(field1,field2,field3) VALUES('values1','value2','value3');

  1. num = patsplit($3$4, arr, /[^,)(;]+/);
    • patsplit(s, a, r)- 分割字符串 s进入数组A在正则表达式上r，并返回字段数。
    • $3$4-$3伊斯兰table1(field1,field2,field3)国。$4​VALUES('values1','value2','value3');因此，连接后，我们得到了这个字符串：table1(field1,field2,field3)VALUES('values1','value2','value3');。
    • arr然后，通过这个正则表达式将其拆分/[^,)(;]+/。它的意思是：除逗号、括号、分号之外的所有字符。因此现在：arr[1]是table1，arr[2]是field1，arr[5]是VALUES，等等。
  2. num /= 2;num对于此脚本中使用的算法，需要变量的一半。
  3. printf("SELECT * FROM %s WHERE ", arr[1]);- 打印 的第一个元素arr，即表名称。在我们的例子中，它是table1.
  4. printf("%s=%s", arr[i], arr[num + i]);- 算法。打印 的前半部分中的一个元素arr以及 的后半部分中的相应元素arr。即，arr[2]和arr[6]，arr[3]和arr[7]，arr[4]和arr[8]。
  5. printf (i < num) ? " AND " : ";\n";- 三元运算符。如果不是最后一次迭代，则打印AND，否则打印;\n（最后的分号和换行符）。

输入（我更正了您的输入，因为它有错误 - 字段名称和字段值的数量不匹配）。

INSERT INTO table1(field1,field2,field3) VALUES('values1','value2','value3');
INSERT INTO table1(field1,field2,field3) VALUES('other_values1','other_value2','other_value3');
INSERT INTO table1(field1,field2,field3) VALUES('another_values1','another_value2','another_value3');
INSERT INTO table2(table2_field1,table2_field2,table2_field3,table2_field4) VALUES('table2_values1','table2_value2','table2_value3','table2_value4');
INSERT INTO table2(table2_field1,table2_field2,table2_field3,table2_field4) VALUES('other_table2_values1','other_table2_value2','other_table2_value3','other_table2_value4');
INSERT INTO table2(table2_field1,table2_field2,table2_field3,table2_field4) VALUES('another_table2_values1','another_table2_value2','another_table2_value3','another_table2_value4');

输出

SELECT * FROM table1 WHERE field1='values1' AND field2='value2' AND field3='value3';
SELECT * FROM table1 WHERE field1='other_values1' AND field2='other_value2' AND field3='other_value3';
SELECT * FROM table1 WHERE field1='another_values1' AND field2='another_value2' AND field3='another_value3';
SELECT * FROM table2 WHERE table2_field1='table2_values1' AND table2_field2='table2_value2' AND table2_field3='table2_value3' AND table2_field4='table2_value4';
SELECT * FROM table2 WHERE table2_field1='other_table2_values1' AND table2_field2='other_table2_value2' AND table2_field3='other_table2_value3' AND table2_field4='other_table2_value4';
SELECT * FROM table2 WHERE table2_field1='another_table2_values1' AND table2_field2='another_table2_value2' AND table2_field3='another_table2_value3' AND table2_field4='another_table2_value4';

相关内容