有时候做开发用到SQL文的时候,由于sql文太长,很难真正看清楚这个sql文的结构.

所以需要一个工具能够自动对SQL文进行排版,在网上有幸找到这个用php写的类能处理这个任务.

原文地址是http://jdorn.github.io/sql-formatter/

SqlFormatter

A PHP class for formatting and highlighting SQL statements.

 

---

Download

If you're using Composer in your project, simply add SqlFormatter to the require section of composer.json

require: {
        "jdorn/sql-formatter": "dev-master" }

Otherwise,  from Github and include lib/SqlFormatter.php in your project.

---

Example Usage

The following example formats and highlights a really complex SQL statement.

    = DATE_FORMAT((DATE_SUB(NOW(),INTERVAL 1 DAY)),'%Y-%c-%d') AND t_create < DATE_FORMAT(NOW(), '%Y-%c-%d') ORDER BY d.id LIMIT 2,10) a, orc_scheme_detail b WHERE a.id = b.id"; echo SqlFormatter::format($sql);

SELECT   DATE_FORMAT(b.t_create, '%Y-%c-%d') dateID,   b.title memo FROM   (    SELECT       id     FROM       orc_scheme_detail d     WHERE       d.business = 208       AND d.type IN (        29, 30, 31, 321, 33, 34, 3542, 361, 327,         38, 39, 40, 41, 42, 431, 4422, 415, 4546,         47, 48, 'a', 29, 30, 31, 321, 33, 34, 3542,         361, 327, 38, 39, 40, 41, 42, 431, 4422,         415, 4546, 47, 48, 'a'      )       AND d.title IS NOT NULL       AND t_create >= DATE_FORMAT(        (          DATE_SUB(NOW(), INTERVAL 1 DAY)        ),         '%Y-%c-%d'      )       AND t_create < DATE_FORMAT(NOW(), '%Y-%c-%d')     ORDER BY       d.id     LIMIT       2,       10  ) a,   orc_scheme_detail b WHERE   a.id = b.id

---

Format Only (No Syntax Highlighting)

If you pass false as the 2nd parameter to the format method, it will output formatted plain text.

SELECT   * FROM   MyTable WHERE   id = 46

---

Syntax Highlighting Only

The highlight method keeps all the original whitespace intact and just adds syntax highlighting.

SELECT * FROM MyTable WHERE id = 46

---

Compress SQL Query

The compress method compresses whitespace and removes comments.

SELECT Id as temp, DateCreated as Created FROM MyTable;

---

Remove SQL Comments

The removeComments method removes all comments, but otherwise preserves the original formatting.

SELECTId as temp, DateCreated as Created FROM MyTable;

---

Split SQL into Individual Queries

The splitQuery method takes a semicolon delimited SQL string and returns an array of the individual queries.

array (  0 => 'DROP TABLE IF EXISTS MyTable;', 1 => 'CREATE TABLE MyTable ( id int );', 2 => 'INSERT INTO MyTable (id) VALUES (1),(2),(3),(4);', 3 => 'SELECT * FROM MyTable;' )

---

Using from the PHP CLI

SqlFormatter will automatically detect when running in CLI mode and will use ASCII shell highlighting instead of HTML.

---

Created by