NAME

SQL::Abstract::mysql - Generate SQL from Perl data structures for MySQL and MariaDB

SYNOPSIS

  use SQL::Abstract::mysql;

  my $abstract = SQL::Abstract::mysql->new(quote_char => chr(96), name_sep => '.');
  # The same as
  use Mojo::mysql;
  my $mysql = Mojo::mysql->new;
  my $abstract = $mysql->abstract;

  say $abstract->insert('some_table', \%some_values, \%some_options);
  say $abstract->select('some_table');

DESCRIPTION

SQL::Abstract::mysql extends SQL::Abstract with a few MySQL / MariaDB features used by Mojo::mysql. It was inspired by SQL::Abstract::Pg.

METHODS

SQL::Abstract::mysql inherits all methods from SQL::Abstract.

insert

  my ($stmt, @bind) = $abstract->insert($table, \@values || \%fieldvals, \%options);

This method extends "insert" in SQL::Abstract with the following functionality:

ON CONFLICT

The on_conflict option can be used to generate INSERT IGNORE, REPLACE and INSERT ... ON DUPLICATE KEY UPDATE queries. So far 'ignore' to pass INSERT IGNORE, 'replace' to pass REPLACE and hash references to pass UPDATE with conflict targets are supported.

  # "insert ignore into t (id, a) values (123, 'b')"
  $abstract->insert('t', {id => 123, a => 'b'}, {on_conflict => 'ignore'});

  # "replace into t (id, a) values (123, 'b')"
  $abstract->insert('t', {id => 123, a => 'b'}, {on_conflict => 'replace'});

  # "insert into t (id, a) values (123, 'b') on duplicate key update c='d'"
  $abstract->insert('t', {id => 123, a => 'b'}, {on_conflict => {c => 'd'}});

select

  my ($stmt, @bind) = $abstract->select($source, $fields, $where, $order);
  my ($stmt, @bind) = $abstract->select($source, $fields, $where, \%options);

This method extends "select" in SQL::Abstract with the following functionality:

AS

The $fields argument accepts array references containing array references with field names and aliases, as well as array references containing scalar references to pass literal SQL and array reference references to pass literal SQL with bind values.

  # "select foo as bar from some_table"
  $abstract->select('some_table', [[foo => 'bar']]);

  # "select foo, bar as baz, yada from some_table"
  $abstract->select('some_table', ['foo', [bar => 'baz'], 'yada']);

  # "select extract(epoch from foo) as foo, bar from some_table"
  $abstract->select('some_table', [\'extract(epoch from foo) as foo', 'bar']);

  # "select 'test' as foo, bar from some_table"
  $abstract->select('some_table', [\['? as foo', 'test'], 'bar']);

JOIN

The $source argument accepts array references containing not only table names, but also array references with tables to generate JOIN clauses for.

  # "select * from foo join bar on (bar.foo_id = foo.id)"
  $abstract->select(['foo', ['bar', foo_id => 'id']]);

  # "select * from foo join bar on (foo.id = bar.foo_id)"
  $abstract->select(['foo', ['bar', 'foo.id' => 'bar.foo_id']]);

  # -left, -right, -inner
  # "select * from foo left join bar on (bar.foo_id = foo.id)"
  $abstract->select(['foo', [-left => 'bar', foo_id => 'id']]);

  # -natural
  # "select * from foo natural join bar"
  $abstract->select(['foo', [-natural => 'bar']]);

  # join using
  # "select * from foo join bar using (foo_id)"
  $abstract->select(['foo', [bar => 'foo_id']]);

  # more than one table
  # "select * from foo join bar on (bar.foo_id = foo.id) join baz on (baz.foo_id = foo.id)"
  $abstract->select(['foo', ['bar', foo_id => 'id'], ['baz', foo_id => 'id']]);

  # more than one field
  # "select * from foo left join bar on (bar.foo_id = foo.id and bar.foo_id2 = foo.id2)"
  $abstract->select(['foo', [-left => 'bar', foo_id => 'id', foo_id2 => 'id2']]);

where

  my ($stmt, @bind) = $abstract->where($where, \%options);

This method extends "where" in SQL::Abstract with the following functionality:

FOR

The for option can be used to generate SELECT queries with FOR UPDATE or LOCK IN SHARE MODE clauses. So far the scalar values update and share and scalar references to pass literal SQL are supported.

  # "select * from some_table for update"
  $abstract->select('some_table', '*', undef, {for => 'update'});

  # "select * from some_table lock in share mode"
  $abstract->select('some_table', '*', undef, {for => 'share'});

  # "select * from some_table for share"
  $abstract->select('some_table', '*', undef, {for => \'share'});

  # "select * from some_table for update skip locked"
  $abstract->select('some_table', '*', undef, {for => \'update skip locked'});

GROUP BY

The group_by option can be used to generate SELECT queries with GROUP BY clauses. So far array references to pass a list of fields and scalar references to pass literal SQL are supported.

  # "select * from some_table group by foo, bar"
  $abstract->select('some_table', '*', undef, {group_by => ['foo', 'bar']});

  # "select * from some_table group by foo, bar"
  $abstract->select('some_table', '*', undef, {group_by => \'foo, bar'});

HAVING

The having option can be used to generate SELECT queries with HAVING clauses, which takes the same values as the $where argument.

  # "select * from t group by a having b = 'c'"
  $abstract->select('t', '*', undef, {group_by => ['a'], having => {b => 'c'}});

ORDER BY

In addition to the $order argument accepted by SQL::Abstract you can pass a hash reference with various options. This includes order_by, which takes the same values as the $order argument.

  # "select * from some_table order by foo desc"
  $abstract->select('some_table', '*', undef, {order_by => {-desc => 'foo'}});

LIMIT / OFFSET

The limit and offset options can be used to generate SELECT queries with LIMIT and OFFSET clauses.

  # "select * from some_table limit 10"
  $abstract->select('some_table', '*', undef, {limit => 10});

  # "select * from some_table offset 5"
  $abstract->select('some_table', '*', undef, {offset => 5});

  # "select * from some_table limit 10 offset 5"
  $abstract->select('some_table', '*', undef, {limit => 10, offset => 5});

SEE ALSO

Mojo::mysql, SQL::Abstract::Pg, Mojolicious::Guides, https://mojolicious.org.

cpanm Mojo::mysql

perl -MCPAN -e shell
install Mojo::mysql