MySQLクエリに改行をつけたい

Question

下記のコードのようにクエリに改行をつけて整形したいのですが、良い方法はないでしょうか？

自分ではこうして大文字での正規表現で考えたのですが、そもそも予約語リストとかを使った方がいいでしょうし、その予約語が例えばINSERTされる値としての使用の意図なのかなどのチェックも必要であることに気づき、自力では無理だと思いました。

もちろん今その機能を書いてくださいなどとは申しませんが、先人のライブラリだとか、ツールだとか、何か良い方法があれば知りたいです。

<?php

function transformQuery($queryString, $orderByField = 'name') {
    // クエリ文字列を大文字に変換して解析
    $upperQueryString = strtoupper($queryString);
    
    if (strpos($upperQueryString, 'SELECT') === 0) {
        // SELECT文の場合
        $pattern = "/SELECT\s*(.*?)\s*FROM\s*(.*?)\s*(WHERE\s*.*?)?\s*(ORDER BY\s*.*?)?$/i";
        preg_match($pattern, $queryString, $matches);
        
        if (count($matches) >= 3) {
            $fields = trim($matches[1]);
            $table = trim($matches[2]);
            $conditions = isset($matches[3]) ? trim($matches[3]) : '';
            $orderBy = isset($matches[4]) ? trim($matches[4]) : '';

            // 新しいクエリを生成して返す
            $newQuery = "SELECT\n{$fields}\nFROM\n{$table}\n";

            if (!empty($conditions)) {
                $newQuery .= "WHERE\n{$conditions}\n";
            }
            
            if (empty($orderBy)) {
                $newQuery .= "ORDER BY {$orderByField}";
            } else {
                $newQuery .= "{$orderBy}";
            }
            
            return $newQuery;
        }
    } elseif (strpos($upperQueryString, 'INSERT') === 0) {
        // INSERT文の場合
        $pattern = "/INSERT\s+INTO\s+(.*?)\s+\((.*?)\)\s+VALUES\s+\((.*?)\)/i";
        preg_match($pattern, $queryString, $matches);
        
        if (count($matches) >= 4) {
            $table = trim($matches[1]);
            $columns = trim($matches[2]);
            $values = trim($matches[3]);

            // 新しいクエリを生成して返す
            $newQuery = "INSERT INTO {$table} ({$columns})\nVALUES ({$values})";
            
            return $newQuery;
        }
    } elseif (strpos($upperQueryString, 'UPDATE') === 0) {
        // UPDATE文の場合
        $pattern = "/UPDATE\s+(.*?)\s+SET\s+(.*?)\s+WHERE\s+(.*)/i";
        preg_match($pattern, $queryString, $matches);
        
        if (count($matches) >= 4) {
            $table = trim($matches[1]);
            $setClause = trim($matches[2]);
            $conditions = trim($matches[3]);

            // 新しいクエリを生成して返す
            $newQuery = "UPDATE {$table}\nSET {$setClause}\nWHERE {$conditions}";
            
            return $newQuery;
        }
    } elseif (strpos($upperQueryString, 'DELETE') === 0) {
        // DELETE文の場合
        $pattern = "/DELETE\s+FROM\s+(.*?)\s+WHERE\s+(.*)/i";
        preg_match($pattern, $queryString, $matches);
        
        if (count($matches) >= 3) {
            $table = trim($matches[1]);
            $conditions = trim($matches[2]);

            // 新しいクエリを生成して返す
            $newQuery = "DELETE FROM {$table}\nWHERE {$conditions}";
            
            return $newQuery;
        }
    }
    
    return $queryString; // 変換できないクエリはそのまま返す
}

// 使用例
$originalQuery1 = "SELECT id, name, age FROM users WHERE age > 18 AND name LIKE 'John%'";
$transformedQuery1 = transformQuery($originalQuery1);
echo $transformedQuery1 . "\n\n";

$originalQuery2 = "INSERT INTO orders (order_date, total_amount) VALUES ('2023-08-12', 150.50)";
$transformedQuery2 = transformQuery($originalQuery2);
echo $transformedQuery2 . "\n\n";

$originalQuery3 = "UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales'";
$transformedQuery3 = transformQuery($originalQuery3);
echo $transformedQuery3 . "\n\n";

$originalQuery4 = "DELETE FROM products WHERE stock_quantity <= 0";
$transformedQuery4 = transformQuery($originalQuery4);
echo $transformedQuery4 . "\n\n";

Answer 1

SqlFormatterというのを作っている人がいます。

こういうものを参考にして、自分の欲しい機能を作成されればよいのではないでしょうか (それか自分でコードを書かずにこれを使うことにするか)。

---

【2023-08-14追記】

yambejp さんのご回答 に対して。

データベースサーバはまさにその「作成済みのクエリ―の評価」をしていますので、それをプログラミングで実現すること自体は可能であるはずです。

また、次のような理由でクエリの整形自体は挑戦しがいのあるテーマとも思いますので、「無意味」と言い切るのもどうかと思います。

質問者さんが質問当初考えていらっしゃったような「正規表現を使ってキーワードの境界を識別する」という方法に限界があるのは明らかです。正規表現では通常、SQLをはじめとするプログラミング言語を表現できるほどの複雑なテキストパターンを扱えないからです。一部の環境でサポートされるいわゆる「拡張正規表現」を使えばできるときもありますが、大変分かりにくく使いにくいものです。

つまり簡単に言うと、正規表現というのは (初心者向けの解説でしばしばそう言われているような) 万能なものでも自由なものでもないのです。ある程度複雑なことをやろうとするならば、むしろ正規表現を使うことは控えたほうがよいです。

SQLのクエリなどのプログラミング言語のテキストを正しく評価するには、「トークン化」(tokenize) と「構文解析」(parse) という2段階の処理が必要です。たとえばカラム名がかりに「TEST FROM」という特殊なものだったときでも、それをひとつのトークンとして識別できれば、それを含むクエリを正しく解析できます。

上で紹介したSqlFormatterは、まさにこれをやっています。トークン化を行ってから、構文規則に基づいて整形やシンタックスハイライティングをしています。そのため、特殊なカラム名なども文法的に正しく表記されていれば正しく扱えます。こういったことは正規表現だけでは実現できないことです。

このようなものを参考にして、質問者さんがやりたいことを実現してみていただきたいです。

Comments

[ThouShaltNot]

まさに求めていたライブラリです。ありがとうございました！利用させて頂きます！

Answer 2

作成済みクエリーを評価するのは運用上もあまり意味がないので単なる改行による整形ツールという位置づけでよいのでしょうか？であれば、予約語を利用して整形するのが妥当でしょう。
ただしカラム名がかりに「TEST FROM」という特殊なものだったときこれも「TEST\nFROM」になるのは仕方がないことです。つまりこれが作成済みのクエリーの評価の無意味なところです。

<?PHP
function transformQuery($sql){
  $pattern=[
    "/(?<=SELECT)\s+/i",
    "/\s+(?=FROM)/i",
    "/\s+(?=WHERE)/i",
    "/\s+(?=ORDER\s+BY)/i",];
  return preg_replace($pattern,"_\n_",$sql);
}
$q1 = "SELECT id, name, age FROM users WHERE age > 18 AND name LIKE 'John%' ORDER  BY id ASC";
$r1 = transformQuery($q1);
print nl2br($r1).";<br>\n";
$q2 = "SELECT id,`TEST FROM`, age FROM `TEST WHERE` WHERE age > 18 AND name LIKE 'John%' ORDER  BY id ASC";
$r2 = transformQuery($q2);
print nl2br($r1).";<br>\n";

追記

ikedasさんからの批判について
フレームワークがクエリービルダーを採用しているように、運用上生のSQL文はトラブルの原因になりがちなため、テーブル名や比較処理など明示的にあたえることが肝要です。もちろん自分で一から書くSQL文の場合組み立て時点で評価できるのでさほど問題はありませんし、値はprepareで渡すのでインジェクションも起きづらいので問題ないでしょう。ただ命題のようにすでにできあがっているSQL文を評価・整形することは無駄しかないのでやめたほうがいい（＝無意味）というのが私見です。とはいえThouShaltNotさんやikedasさんがテキストとしての生のSQL文を重要視しているのであればそれを否定するものではありませんし、その部分については先の回答で肯定的に回答を上げているつもりです。
例はよくありませんがイメージとしてはevalで実行させたいPHP文を、実行前に有効かどうか評価すべきか・・・的な話で、私は評価するなんて無駄（＝無意味）なぜならevalなんて使わないから、という考え方です。
極端な例で恐縮ですが参考までに以下

create table `select test from tbl`(id int primary key,`select test from tbl` varchar(100));
insert into `select test from tbl` values
(1,'test'),
(2,'select test from tbl'),
(3,'select test from tbl where 1'),
(4,'select test from tbl');

create table `select test from tbl where 1` (id int primary key,`select test from tbl` varchar(100));
insert into `select test from tbl where 1` values
(1,'select test from tbl'),
(2,'select test from tbl'),
(3,'select test from tbl where 1');

としたとき、以下のSQL文は有効ですが、テキストとして評価をすべきかというと疑問です

select `select test from tbl` as '`select test from tbl where 1' 
from `select test from tbl` as `select test from tbl where 1` 
inner join `select test from tbl where 1`  as ```select test from tbl```
using (`select test from tbl`)
where `select test from tbl`='select test from tbl' 
order by `select test from tbl`

Comments

[ThouShaltNot]

ありがとうございます！質問のコードよりだいぶ良いですね。