README

该库旨在成为构建、拆分和修改MySQL查询的终极工具。

自动智能引号有助于防止SQL注入和保留关键字问题。

查询构建器可以独立使用，也可以与Jasny的DB层一起使用，或在几乎任何框架中使用。

安装

Jasny的MySQL查询构建器在Packagist上注册为jasny/dbquery-mysql，可以使用composer轻松安装。

composer install jasny/dbquery-mysql

或者您可以简单地下载.zip文件，并将“src”文件夹中的文件复制过来。

示例

一个简单的示例，说明如何使用查询构建器

use Jasny\DB\MySQL\Query;

$query = Query::select()->columns('id', 'name')->from('foo')->where('active = 1');
$result = $mysqli->query($query); // SELECT `id`, `name` FROM `foo` WHERE `active` = 1

在查询上动态应用分页和过滤

use Jasny\DB\MySQL\Query;

$query = new Query("SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.id WHERE active = 1 LIMIT 25");
if (isset($_GET['page'])) $query->page(3);

$filter = isset($_POST['filter']) ? $_POST['filter'] : array(); // array('type' => 'bike', 'price between ? and ?' => array(10, 20))
foreach ($filter as $field => $value) {
    $query->where($field, $value);
}

$result = $mysqli->query($query); // SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.id WHERE (active = 1) AND (`type` = "bike") AND (`price` between 10 and 20) LIMIT 25 OFFSET 50

为INSERT INTO ... SELECT ... ON DUPLICATE KEY查询映射字段

use Jasny\DB\MySQL\Query;

$columns = array(
    'ref' => 'ref',
    'man' => 'boy',
    'woman' => 'girl',
    'amount' => 'SUM(z.bucks)'
);

$select = Query::select()->columns($columns)->from('foo')->innerJoin('z', 'foo.id = z.foo_id')->groupBy('foo.id');
$insert = Query::insert()->into('abc')->columns(array_keys($columns))->set($select)->onDuplicateKeyUpdate();

$mysql->query($insert); // INSERT INTO `abc` (`ref`, `man`, `woman`, `amount`)
                        //  SELECT `ref` AS `ref`, `boy` AS `man`, `girl` AS `woman`, SUM(`z`.`bucks`) AS `amount` FROM `foo` LEFT JOIN `z` ON `foo`.`id` = `z`.`foo_id` GROUP BY `foo`.id`
                        //  ON DUPLICATE KEY UPDATE `ref` = VALUES(`ref`), `man` = VALUES(`man`), `woman` = VALUES(`woman`), `amount` = VALUES(`amount`)

API文档（生成版）

http://jasny.github.com/dbquery-mysql/docs