README.md
1# NAME
2
3SQL::Abstract::Plugin::InsertMulti - add mysql bulk insert supports for SQL::Abstract
4
5# SYNOPSIS
6
7 use SQL::Abstract;
8 use SQL::Abstract::Plugin::InsertMulti;
9
10 my $sql = SQL::Abstract->new;
11 my ($stmt, @bind) = $sql->insert_multi('people', [
12 +{ name => 'foo', age => 23, },
13 +{ name => 'bar', age => 40, },
14 ]);
15
16# DESCRIPTION
17
18SQL::Abstract::Plugin::InsertMulti is enable bulk insert support for [SQL::Abstract](http://search.cpan.org/perldoc?SQL::Abstract). Declare 'use SQL::Abstract::Plugin::InsertMulti;' with 'use SQL::Abstract;',
19exporting insert\_multi() and update\_multi() methods to [SQL::Abstract](http://search.cpan.org/perldoc?SQL::Abstract) namespace from SQL::Abstract::Plugin::InsertMulti.
20Plugin system is depends on 'into' options of [Sub::Exporter](http://search.cpan.org/perldoc?Sub::Exporter).
21
22Notice: please check your mysql\_allow\_packet parameter using this module.
23
24# METHODS
25
26## insert\_multi($table, \\@data, \\%opts)
27
28 my ($stmt, @bind) = $sql->insert_multi('foo', [ +{ a => 1, b => 2, c => 3 }, +{ a => 4, b => 5, c => 6, }, ]);
29 # $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? )|
30 # @bind = (1, 2, 3, 4, 5, 6);
31
32@data is HashRef list.
33%opts details is below.
34
35- ignore
36
37 Use 'INSERT IGNORE' instead of 'INSERT INTO'.
38
39- update
40
41 Use 'ON DUPLICATE KEY UPDATE'.
42 This value is same as update()'s data parameters.
43
44- update\_ignore\_fields
45
46 update\_multi() method is auto generating 'ON DUPLICATE KEY UPDATE' parameters:
47
48 my ($stmt, @bind) = $sql->update_multi('foo', [qw/a b c/], [ [ 1, 2, 3 ], [ 4, 5, 6 ] ]);
49 # $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? ) ON DUPLICATE KEY UPDATE a = VALUES( a ), b = VALUES( b ), c = VALUES( c )|
50 # @bind = (1, 2, 3, 4, 5, 6);
51
52 given update\_ignore\_fields,
53
54 my ($stmt, @bind) = $sql->update_multi('foo', [qw/a b c/], [ [ 1, 2, 3 ], [ 4, 5, 6 ] ], +{ update_ignore_fields => [qw/b c/], });
55 # $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? ) ON DUPLICATE KEY UPDATE a = VALUES( a )|
56 # @bind = (1, 2, 3, 4, 5, 6);
57
58## insert\_multi($table, \\@field, \\@data, \\%opts)
59
60 my ($stmt, @bind) = $sql->insert_multi('foo', [qw/a b c/], [ [ 1, 2, 3 ], [ 4, 5, 6 ] ]);
61 # $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? )|
62 # @bind = (1, 2, 3, 4, 5, 6);
63
64@data is ArrayRef list. See also ["insert\_multi($table, \\@data, \\%opts)"](#insert\_multi($table, \\@data, \\%opts)) %opts details.
65
66## update\_multi($table, \\@data, \\%opts)
67
68@data is HashRef list. See also ["insert\_multi($table, \\@data, \\%opts)"](#insert\_multi($table, \\@data, \\%opts)) %opts details.
69
70 my ($stmt, @bind) = $sql->update_multi('foo', [ [ 1, 2, 3 ], [ 4, 5, 6 ] ]);
71 # $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? ) ON DUPLICATE KEY UPDATE a = VALUES( a ), b = VALUES( b ), c = VALUES( c )|
72 # @bind = (1, 2, 3, 4, 5, 6);
73
74## update\_multi($table, \\@field, \\@data, \\%opts)
75
76 my ($stmt, @bind) = $sql->update_multi('foo', [qw/a b c/], [ +{ a => 1, b => 2, c => 3 }, +{ a => 4, b => 5, c => 6, }, ]);
77 # $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? ) ON DUPLICATE KEY UPDATE a = VALUES( a ), b = VALUES( b ), c = VALUES( c )|
78 # @bind = (1, 2, 3, 4, 5, 6);
79
80@data is ArrayRef list. See also ["insert\_multi($table, \\@data, \\%opts)"](#insert\_multi($table, \\@data, \\%opts)) %opts details.
81
82# AUTHOR
83
84Toru Yamaguchi <zigorou@cpan.org>
85
86Thanks ma.la [http://subtech.g.hatena.ne.jp/mala/](http://subtech.g.hatena.ne.jp/mala/). This module is based on his source codes.
87
88# SEE ALSO
89
90- http://subtech.g.hatena.ne.jp/mala/20090729/1248880239
91- http://gist.github.com/158203
92- [SQL::Abstract](http://search.cpan.org/perldoc?SQL::Abstract)
93- [Sub::Exporter](http://search.cpan.org/perldoc?Sub::Exporter)
94
95# LICENSE
96
97This library is free software; you can redistribute it and/or modify
98it under the same terms as Perl itself.
99