Laravel/Lumen 扩展DB Builder语法

截至5.6版本,Laravel的DB Builder都没有方法实现忽略插入插入或更新5.7版本新增updateOrInsert())。除了自己封装多一层之外,还有一个更优雅的解决方案是对Builder语法进行扩展。

扩展支持的语法

这里要扩展的是三个常用的语法:

  1. INSERT IGNORE INTO
  2. REPLACE INTO
  3. INSERT INTO ... ON DUPLICATE KEY UPDATE

扩展Builder类

Laravel官方已经在Illuminate\Database包中提供了扩展实例,需要创建自定义的GrammarBuilderConnection类,然后由服务提供者注册。

自定义Grammar

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
<?php

namespace App\Library\Database\Query\Grammars;

use Illuminate\Database\Query\Grammars\MySqlGrammar as Grammar;
use App\Library\Database\Query\MySqlBuilder as Builder;

class MySqlGrammar extends Grammar
{
/**
* Compile a replace into statement into SQL.
*
* @link https://dev.mysql.com/doc/refman/5.5/en/replace.html
*
* @param \App\Library\Database\Query\MySqlBuilder $query
* @param array $values
* @return string
*/
public function compileReplace(Builder $query, array $values)
{
// Essentially we will force every insert to be treated as a batch insert which
// simply makes creating the SQL easier for us since we can utilize the same
// basic routine regardless of an amount of records given to us to insert.
$table = $this->wrapTable($query->from);

if (!is_array(reset($values))) {
$values = [$values];
}

$columns = $this->columnize(array_keys(reset($values)));

// We need to build a list of parameter place-holders of values that are bound
// to the query. Each insert should have the exact same amount of parameter
// bindings so we will loop through the record and parameterize them all.
$parameters = [];

foreach ($values as $record) {
$parameters[] = '(' . $this->parameterize($record) . ')';
}

$parameters = implode(', ', $parameters);

return "REPLACE INTO $table ($columns) VALUES $parameters";
}

/**
* Compile an insert ignore statement into SQL.
*
* @link https://dev.mysql.com/doc/refman/5.5/en/insert.html
*
* @param \App\Library\Database\Query\MySqlBuilder $query
* @param array $values
* @return string
*/
public function compileInsertIgnore(Builder $query, array $values)
{
// Essentially we will force every insert to be treated as a batch insert which
// simply makes creating the SQL easier for us since we can utilize the same
// basic routine regardless of an amount of records given to us to insert.
$table = $this->wrapTable($query->from);

if (!is_array(reset($values))) {
$values = [$values];
}

$columns = $this->columnize(array_keys(reset($values)));

// We need to build a list of parameter place-holders of values that are bound
// to the query. Each insert should have the exact same amount of parameter
// bindings so we will loop through the record and parameterize them all.
$parameters = [];

foreach ($values as $record) {
$parameters[] = '(' . $this->parameterize($record) . ')';
}

$parameters = implode(', ', $parameters);

return "INSERT IGNORE INTO $table ($columns) VALUES $parameters";
}

/**
* Compile an insert update statement into SQL.
*
* @link https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
* @link https://gist.github.com/RuGa/5354e44883c7651fd15c
*
* @param \App\Library\Database\Query\MySqlBuilder $query
* @param array $values
* @return string
*/
public function compileInsertUpdate(Builder $query, array $values)
{
// Essentially we will force every insert to be treated as a batch insert which
// simply makes creating the SQL easier for us since we can utilize the same
// basic routine regardless of an amount of records given to us to insert.
$table = $this->wrapTable($query->from);

$columnNames = array_keys(reset($values));

$columns = $this->columnize($columnNames);

$parameters = implode(',', array_map(function ($row) {
return '(' . $this->parameterize($row) . ')';
}, $values));

$updates = implode(',', array_map(function ($columnName) {
return $this->wrap($columnName) . ' = VALUES(' . $this->wrap($columnName) . ')';
}, $columnNames));

return "INSERT INTO $table ($columns) VALUES $parameters ON DUPLICATE KEY UPDATE $updates";
}
}

自定义Builder

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
<?php

namespace App\Library\Database\Query;

use Illuminate\Database\Query\Builder as QueryBuilder;

class MySqlBuilder extends QueryBuilder
{
/**
* The database query grammar instance.
*
* @var Grammars\MySqlGrammar
*/
public $grammar;

/**
* Insert a new record into the database, replace on primary key conflict.
*
* @param array $values
* @return bool
*/
public function replace(array $values)
{
if (empty($values)) {
return true;
}

// Since every insert gets treated like a batch insert, we will make sure the
// bindings are structured in a way that is convenient for building these
// inserts statements by verifying the elements are actually an array.
if (!is_array(reset($values))) {
$values = [$values];
}

// Since every insert gets treated like a batch insert, we will make sure the
// bindings are structured in a way that is convenient for building these
// inserts statements by verifying the elements are actually an array.
else {
foreach ($values as $key => $value) {
ksort($value);
$values[$key] = $value;
}
}

// We'll treat every insert like a batch insert so we can easily insert each
// of the records into the database consistently. This will make it much
// easier on the grammars to just handle one type of record insertion.
$bindings = [];

foreach ($values as $record) {
foreach ($record as $value) {
$bindings[] = $value;
}
}

$sql = $this->grammar->compileReplace($this, $values);

// Once we have compiled the insert statement's SQL we can execute it on the
// connection and return a result as a boolean success indicator as that
// is the same type of result returned by the raw connection instance.
$bindings = $this->cleanBindings($bindings);

return $this->connection->insert($sql, $bindings);
}

/**
* Insert a new record into the database, update on primary key conflict.
*
* @param array $values
* @return bool
*/
public function insertUpdate(array $values)
{
if (empty($values)) {
return true;
}

// Since every insert gets treated like a batch insert, we will make sure the
// bindings are structured in a way that is convenient for building these
// inserts statements by verifying the elements are actually an array.
if (!is_array(reset($values))) {
$values = [$values];
} // Sort the keys in each row alphabetically for consistency
else {
foreach ($values as $key => $value) {
ksort($value);
$values[$key] = $value;
}
}

// We'll treat every insert like a batch insert so we can easily insert each
// of the records into the database consistently. This will make it much
// easier on the grammars to just handle one type of record insertion.
$bindings = [];

foreach ($values as $record) {
foreach ($record as $value) {
$bindings[] = $value;
}
}

$sql = $this->grammar->compileInsertUpdate($this, $values);

// Once we have compiled the insert statement's SQL we can execute it on the
// connection and return a result as a boolean success indicator as that
// is the same type of result returned by the raw connection instance.

$bindings = $this->cleanBindings($bindings);

return $this->connection->insert($sql, $bindings);
}

/**
* Insert a new record into the database, discard on primary key conflict.
*
* @param array $values
* @return bool
*/
public function insertIgnore(array $values)
{
if (empty($values)) {
return true;
}

// Since every insert gets treated like a batch insert, we will make sure the
// bindings are structured in a way that is convenient for building these
// inserts statements by verifying the elements are actually an array.
if (!is_array(reset($values))) {
$values = [$values];
}

// Since every insert gets treated like a batch insert, we will make sure the
// bindings are structured in a way that is convenient for building these
// inserts statements by verifying the elements are actually an array.
else {
foreach ($values as $key => $value) {
ksort($value);
$values[$key] = $value;
}
}

// We'll treat every insert like a batch insert so we can easily insert each
// of the records into the database consistently. This will make it much
// easier on the grammars to just handle one type of record insertion.
$bindings = [];

foreach ($values as $record) {
foreach ($record as $value) {
$bindings[] = $value;
}
}

$sql = $this->grammar->compileInsertIgnore($this, $values);

// Once we have compiled the insert statement's SQL we can execute it on the
// connection and return a result as a boolean success indicator as that
// is the same type of result returned by the raw connection instance.

$bindings = $this->cleanBindings($bindings);

return $this->connection->insert($sql, $bindings);
}
}

自定义Connection

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?php

namespace App\Library\Database;

use App\Library\Database\Query\MySqlBuilder as Builder;
use App\Library\Database\Query\Grammars\MySqlGrammar as QueryGrammar;
use Illuminate\Database\MySqlConnection as Connection;


class MySqlConnection extends Connection
{
/**
* Get the default query grammar instance.
*
* @return \Illuminate\Database\Grammar|\Illuminate\Database\Query\Grammars\MySqlGrammar
*/
protected function getDefaultQueryGrammar()
{
return $this->withTablePrefix(new QueryGrammar);
}

/**
* Get a new query builder instance.
*
* @return \App\Library\Database\Query\MySqlBuilder
*/
public function query()
{
return new Builder(
$this, $this->getQueryGrammar(), $this->getPostProcessor()
);
}
}

自定义Provider

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?php

namespace App\Providers;

use App\Library\Database\MySqlConnection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
/**
* Override the default connection for MySQL. This allows us to use `replace` etc.
*
* @link https://stidges.com/extending-the-connection-class-in-laravel
* @link https://gist.github.com/VinceG/0fb570925748ab35bc53f2a798cb517c
*
* @return void
*/
public function boot()
{
Connection::resolverFor('mysql', function ($connection, $database, $prefix, $config) {
return new MySqlConnection($connection, $database, $prefix, $config);
}); // 5.4及以上版本使用此方式绑定
}

/**
* Register the service provider.
*
* @return void
*/
public function register()
{
$this->app->bind('db.connection.mysql', MySqlConnection::class); // 5.4版本以下使用此方式绑定
}
}

注册Provider

Laravel

config/app.php里的providers中添加新的Provider类:

1
2
3
4
5
'providers' => [
// 其他的服务提供者

App\Providers\AppServiceProvider::class,
],

Lumen

bootstrap/app.php中手动绑定:

1
2
3
4
// 扩展的查询语法:Replace、InsertIgnore、InsertUpdate
$app->register(App\Providers\DatabaseServiceProvider::class);

return $app;

使用Demo

1
2
3
4
5
$data = [
'name' => 'soap',
'status' => 'kia'
];
\DB::connection('cod8')->table('141_members')->insertUpdate($data);

注意这里输入\DB::connection()->table()->之后IDE不会提示扩展的方法,只能在封装类中声明是自定义的Builder以获得提示。
image
QQ截图20190412161418

封装成组件

参照使用satis搭建私有Composer库

Refrences

https://stidges.com/extending-the-connection-class-in-laravel
https://gist.github.com/VinceG/0fb570925748ab35bc53f2a798cb517c
https://github.com/art-institute-of-chicago/data-service-images