jasny / persist-sql-query
SQL 查询构建器和解析器
v3.0.0-alpha1
2020-10-21 23:53 UTC
Requires
- php: >=7.4.0
Requires (Dev)
- jasny/php-code-quality: 2.6.*
This package is auto-updated.
Last update: 2024-08-30 00:10:22 UTC
README
持久化 - SQL 查询
这个库旨在成为构建、分割和修改查询的终极工具。
自动智能引号有助于防止 SQL 注入和保留关键字问题。
支持的 SQL 方言
- 通用(ANSI 标准)
- MySQL
- PostgreSQL (待定)
- SQLite (待定)
安装
composer install jasny/persist-sql-query
示例
向 SELECT
查询添加 WHERE
条件和 OFFSET
use Jasny\Persist\SQL\Query; $query = (new Query("SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.id WHERE active = 1 LIMIT 25")) ->where('type', 'bike') ->where('price between ? and ?', [10, 20]) ->page(3); echo $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\Persist\SQL\Query; $columns = [ 'ref' => 'ref', 'man' => 'boy', 'woman' => 'girl', 'amount' => 'SUM(z.bucks)' ]; $build = Query::build('mysql'); $select = $build->select()->columns($columns)->from('foo')->innerJoin('z', 'foo.id = z.foo_id')->groupBy('foo.id'); $insert = $build->insert()->into('abc')->columns(array_keys($columns))->set($select)->onDuplicateKeyUpdate(); echo $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`)