man > DBD::mysql

man perldoc info pydoc3 ri search

📖 NAME

DBD::mysql - MySQL driver for the Perl5 Database Interface (DBI)

🚀 Quick Reference

Use Case	Command	Description
Connect to database	DBI->connect("DBI:mysql:database=$db;host=$host", $user, $pass, {RaiseError=>1})	🔗 Open a MySQL connection with DSN and options
Execute a non-SELECT query	$dbh->do("INSERT INTO foo VALUES (?, ?)", undef, $val1, $val2)	✏️ INSERT/UPDATE/DELETE with placeholders
Prepare and execute SELECT	$sth = $dbh->prepare("SELECT * FROM t WHERE id=?"); $sth->execute($id);	🔍 Retrieve rows safely
Fetch results as hash	while ($row = $sth->fetchrow_hashref()) { ... }	📄 Access columns by name
Transaction control	$dbh->{AutoCommit} = 0; $dbh->commit(); $dbh->rollback();	🔄 Enable/disable auto‑commit, commit/rollback
Use server‑side prepared statements	DBI->connect("DBI:mysql:test;mysql_server_prepare=1", ...)	⚡ Boost performance for repeated statements
Async query	$dbh->do($sql, { async=>1 }); $dbh->mysql_async_ready(); $dbh->mysql_async_result()	⏳ Run long query without blocking

📝 SYNOPSIS

use DBI;

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password);

my $sth = $dbh->prepare(
    'SELECT id, first_name, last_name FROM authors WHERE last_name = ?')
    or die "prepare statement failed: $dbh->errstr()";
$sth->execute('Eggers') or die "execution failed: $dbh->errstr()";
print $sth->rows . " rows found.\n";
while (my $ref = $sth->fetchrow_hashref()) {
    print "Found a row: id = $ref->{'id'}, fn = $ref->{'first_name'}\n";
}
$sth->finish;

💡 EXAMPLE

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost",
                        "joe", "joe's password",
                        {'RaiseError' => 1});

# Drop table 'foo'. This may fail, if 'foo' doesn't exist
# Thus we put an eval around it.
eval { $dbh->do("DROP TABLE foo") };
print "Dropping foo failed: $@\n" if $@;

# Create a new table 'foo'. This must not fail, thus we don't
# catch errors.
$dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))");

# INSERT some data into 'foo'. We are using $dbh->quote() for
# quoting the name.
$dbh->do("INSERT INTO foo VALUES (1, " . $dbh->quote("Tim") . ")");

# same thing, but using placeholders (recommended!)
$dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen");

# now retrieve data from the table.
my $sth = $dbh->prepare("SELECT * FROM foo");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
  print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n";
}
$sth->finish();

# Disconnect from the database.
$dbh->disconnect();

📚 DESCRIPTION

DBD::mysql is the Perl5 Database Interface driver for the MySQL database. It provides an interface between Perl and the MySQL programming API. Most functions of the API are supported.

From Perl you activate the interface with use DBI;. Then you can connect to MySQL servers and send queries via an object oriented interface. Two types of objects: database handles and statement handles.

Database handle from connect:

$dbh = DBI->connect("DBI:mysql:database=$db;host=$host",
  $user, $password, {RaiseError => 1});

Execute SQL statements:

my $query = sprintf("INSERT INTO foo VALUES (%d, %s)",
                     $number, $dbh->quote("name"));
$dbh->do($query);

Or with placeholders (recommended):

$dbh->do("INSERT INTO foo VALUES (?, ?)", undef,
         $number, $name);

Retrieve results with a statement handle:

$sth = $dbh->prepare("SELECT * FROM $table");
$sth->execute();
my $row = $sth->fetchrow_hashref();

🔧 Class Methods

🔌 connect

use DBI;

$dsn = "DBI:mysql:$database";
$dsn = "DBI:mysql:database=$database;host=$hostname";
$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";

$dbh = DBI->connect($dsn, $user, $password);

The database is not required, but without it you must prefix tables with the database name (e.g., SELECT * FROM mydb.mytable). SELECT DATABASE() returns the current database.

host, port: Defaults to local machine via UNIX socket. Use 127.0.0.1 for TCP/IP on localhost. For IPv6, use bracketed address: host=[1a12:2800:6f2:85::f20:8cf];port=3306.

mysql_client_found_rows: Enables CLIENT_FOUND_ROWS. When true, UPDATE returns number of rows matching the WHERE clause, not rows changed.

mysql_compression: Set to 1 to compress client‑server communication.

mysql_connect_timeout: Seconds before connection timeout.

mysql_write_timeout: Seconds before write operation timeout.

mysql_read_timeout: Seconds before read operation timeout.

mysql_init_command: SQL statement executed on connect (and on reconnect).

mysql_skip_secure_auth: For older MySQL databases without secure auth.

mysql_read_default_file, mysql_read_default_group: Read config file like /etc/my.cnf. Example:

$dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
$dbh = DBI->connect($dsn, $user, $password);

The mysql_read_default_group specifies the group (default client).

mysql_socket: Choose the Unix socket path (e.g., mysql_socket=/dev/mysql).

mysql_ssl: Set to 1 to enforce SSL encryption. Requires additional SSL options:

mysql_ssl=1 mysql_ssl_verify_server_cert=1 mysql_ssl_ca_file=/path/to/ca_cert.pem

mysql_ssl_ca_file: Path to PEM file with trusted CA certificates.

mysql_ssl_ca_path: Directory with trusted CA certificates in PEM format (OpenSSL only).

mysql_ssl_verify_server_cert: Checks server’s Common Name against hostname (prevents MITM).

mysql_ssl_client_key: PEM key file for SSL.

mysql_ssl_client_cert: PEM certificate file for SSL.

mysql_ssl_cipher: List of permissible ciphers (e.g., AES128-SHA).

mysql_ssl_optional: Set to true to make SSL optional (security risk). Default false.

mysql_server_pubkey: Path to server’s RSA public key for sha256_password or caching_sha2_password.

mysql_get_server_pubkey: Set to true to request the server’s public RSA key.

mysql_local_infile: Set to 1 to enable LOAD DATA LOCAL (if server allows).

mysql_multi_statements: Enable multiple statements separated by semicolon. May conflict with server‑side prepared statements.

mysql_server_prepare: Enable server‑side prepared statements. Example:

$dbh = DBI->connect(
  "DBI:mysql:database=test;host=localhost;mysql_server_prepare=1",
  "", "", { RaiseError => 1, AutoCommit => 1 }
);

Falls back to non‑prepared if server cannot prepare the statement.

mysql_server_prepare_disable_fallback: Disable fallback to non‑prepared; error propagated if statement cannot be prepared.

mysql_embedded_options: Pass command‑line options to embedded server (e.g., --help,--verbose).

mysql_embedded_groups: Specify config file groups for embedded server (default: [server], [embedded]).

mysql_conn_attrs: Hash of custom connection attributes. Example:

my $dbh= DBI->connect($dsn, $user, $password,
  { AutoCommit => 0,
    mysql_conn_attrs => { foo => 'bar', wiz => 'bang' },
  });

Attributes are visible in performance_schema.session_connect_attrs. Predefined attributes include _os, _platform, _client_name, _client_version, and program_name.

🔍 Private MetaData Methods

📋 ListDBs

my $drh = DBI->install_driver("mysql");
@dbs = $drh->func("$hostname:$port", '_ListDBs');
@dbs = $drh->func($hostname, $port, '_ListDBs');
@dbs = $dbh->func('_ListDBs');

Returns a list of all databases on the server. Legacy; prefer @dbs = DBI->data_sources("mysql").

🗄️ DATABASE HANDLES

Read‑only attributes (correspond to MySQL C API functions):

$errno = $dbh->{'mysql_errno'};
$error = $dbh->{'mysql_error'};
$info = $dbh->{'mysql_hostinfo'};
$info = $dbh->{'mysql_info'};
$insertid = $dbh->{'mysql_insertid'};
$info = $dbh->{'mysql_protoinfo'};
$info = $dbh->{'mysql_serverinfo'};
$info = $dbh->{'mysql_stat'};
$threadId = $dbh->{'mysql_thread_id'};

mysql_clientinfo: Client library version string (e.g., 5.2.0-MariaDB).

mysql_clientversion: Numeric client version (e.g., 50200).

mysql_serverversion: Numeric server version.

mysql_dbd_stats: Hash with stats:

• auto_reconnects_ok – number of successful reconnects
• auto_reconnects_failed – number of failed reconnects

Read/write attributes:

mysql_auto_reconnect: Automatically reconnect if connection lost. Default off, but on if GATEWAY_INTERFACE or MOD_PERL is set. Disabled when AutoCommit is off. Use with caution (table locks lost).

mysql_use_result: Use mysql_use_result (faster, less memory, but blocks). Default is mysql_store_result. Set via DSN or after handle creation:

$dbh = DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
$dbh->{mysql_use_result} = 1;

mysql_enable_utf8: Assume strings are UTF‑8. When set, retrieved text columns get the UTF‑8 flag. Also tells MySQL incoming data is UTF‑8 (only effective if set during connect).

mysql_enable_utf8mb4: Like mysql_enable_utf8, but supports 4‑byte UTF‑8 characters.

mysql_bind_type_guessing: For emulated prepared statements, tries to guess if a bound value is numeric and omits quoting. May affect index usage. Set via DSN or after connect.

mysql_bind_comment_placeholders: Bind placeholders inside comments (non‑standard).

mysql_no_autocommit_cmd: Suppress issuing SET autocommit. Useful with MySQL Proxy.

ping: Send a ping to the server: $rc = $dbh->ping().

📄 STATEMENT HANDLES

Attributes valid after execute (unless noted). Access via $sth->{ATTR_NAME}.

mysql_use_result: Force mysql_use_result for this statement. Set at prepare or after:

my $sth = $dbh->prepare("QUERY", { mysql_use_result => 1});
$sth->{mysql_use_result} = 1;

ChopBlanks: Chop leading/trailing blanks from column values on fetch.

mysql_gtids: Returns GTID(s) if session tracking is enabled.

mysql_insertid: Value of AUTO_INCREMENT column from last INSERT (if automatically generated). Access via $sth->{mysql_insertid} (preferred over $dbh->{mysql_insertid}).

mysql_is_blob: Reference to array of booleans; TRUE for blob columns.

mysql_is_key: Reference to array of booleans; TRUE for key columns.

mysql_is_num: Reference to array of booleans; TRUE for numeric columns.

mysql_is_pri_key: Reference to array of booleans; TRUE for primary key columns.

mysql_is_auto_increment: Reference to array of booleans; TRUE for AUTO_INCREMENT columns.

mysql_length, mysql_max_length: References to arrays of column sizes. max_length is the maximum in the result; length is theoretical maximum.

NAME: Reference to array of column names.

NULLABLE: Reference to array of booleans; TRUE if column may contain NULL.

NUM_OF_FIELDS: Number of fields in SELECT result. Zero for non‑SELECT statements.

mysql_table: Reference to array of table names (useful in JOIN results).

TYPE: Reference to array of portable column types (e.g., DBI::SQL_INTEGER).

mysql_type: Reference to array of MySQL native column types (e.g., DBD::mysql::FIELD_TYPE_SHORT).

mysql_type_name: Array of MySQL type names (ANSI SQL names preferred).

mysql_warning_count: Number of warnings from the last statement execution. Available on both statement and database handles.

🔄 TRANSACTION SUPPORT

• Default AutoCommit is on.
• $dbh->{AutoCommit} = 0 sets server autocommit=0; switching to 1 issues COMMIT.
• $dbh->rollback() and $dbh->commit() issue ROLLBACK/COMMIT. Rollback also occurs on DESTROY if AutoCommit is off.
• Never manually set server autocommit.
• Changing AutoCommit may fail; check for errors (use RaiseError or manual check).
• If error occurs, disconnect and reconnect.
• Reconnect feature is disabled when AutoCommit is off.
• mysql_auto_reconnect can be toggled; turn off if using LOCK TABLE.

📊 MULTIPLE RESULT SETS

Supported via $sth->more_results. Basic usage:

do
{
  while (@row = $sth->fetchrow_array())
  {
    do stuff;
  }
} while ($sth->more_results)

Example:

$dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
$dbh->do("create procedure someproc() deterministic
 begin
 declare a,b,c,d int;
 set a=1; set b=2; set c=3; set d=4;
 select a, b, c, d;
 select d, c, b, a;
 select b, a, c, d;
 select c, b, d, a;
 end") or print $DBI::errstr;

$sth=$dbh->prepare('call someproc()') || die $DBI::err.": ".$DBI::errstr;
$sth->execute || die DBI::err.": ".$DBI::errstr; $rowset=0;
do {
  print "\nRowset ".++$i."\n---------------------------------------\n\n";
  foreach $colno (0..$sth->{NUM_OF_FIELDS}-1) {
    print $sth->{NAME}->[$colno]."\t";
  }
  print "\n";
  while (@row= $sth->fetchrow_array())  {
    foreach $field (0..$#row) {
      print $row[$field]."\t";
    }
    print "\n";
  }
} until (!$sth->more_results)

⚠️ Issues with multiple result sets

Jagged result sets (varying number of columns) may cause script crashes.

🧵 MULTITHREADING

Thread safety depends on underlying C libraries. DBD::mysql is believed to be completely thread safe if the C libraries are thread safe and handles are not shared among threads. MySQL C libraries are thread safe since MySQL 5.5.

⚡ ASYNCHRONOUS QUERIES

Allows one asynchronous query per connection. Start by setting async => 1 in do or prepare. Additional methods: mysql_async_result, mysql_async_ready, mysql_fd.

Example:

use feature 'say';
$dbh->do('SELECT SLEEP(10)', { async => 1 });
until($dbh->mysql_async_ready) {
  say 'not ready yet!';
  sleep 1;
}
my $rows = $dbh->mysql_async_result;

📦 INSTALLATION

See DBD::mysql::INSTALL.

👤 AUTHORS

Originally a non‑DBI driver, Mysql, was written by Andreas König. The first DBD::mysql was developed by Alligator Descartes, with help from Gary Shea, Andreas König, and Tim Bunce. Current incarnation by Jochen Wiedmann, with bug‑fixes by Rudy Lippan, prepared statement support by Patrick Galbraith and Alexy Stroganov (embedded server support by Stroganov). Maintained for the past nine years by Patrick Galbraith (patg@patg.net) and Michiel Beijen (michiel.beijen@gmail.com), plus community contributions.

🤝 CONTRIBUTIONS

Source code at https://github.com/perl5-dbi/DBD-mysql/. Fork or create a diff. All contributions welcome.

©️ COPYRIGHT

• Large Portions Copyright (c) 2004‑2013 Patrick Galbraith
• Large Portions Copyright (c) 2004‑2006 Alexey Stroganov
• Large Portions Copyright (c) 2003‑2005 Rudolf Lippan
• Large Portions Copyright (c) 1997‑2003 Jochen Wiedmann
• Copyright (c)1994‑1997 their original authors

📜 LICENSE

Released under the same license as Perl itself. See http://www.perl.com/perl/misc/Artistic.html.

📧 MAILING LIST SUPPORT

Subscribe to dbi‑users: dbi-users-subscribe@perl.org. Archives at http://groups.google.com/group/perl.dbi.users.

ℹ️ ADDITIONAL DBI INFORMATION

See http://dbi.perl.org for documentation, mailing lists, and latest modules. Perldoc: perldoc DBI and perldoc DBD::mysql.

🐛 BUG REPORTING, ENHANCEMENT/FEATURE REQUESTS

Report bugs at https://rt.cpan.org/Dist/Display.html?Name=DBD-mysql with version information. This driver is maintained solely by the community.

📖 NAME 🚀 Quick Reference 📝 SYNOPSIS 💡 EXAMPLE 📚 DESCRIPTION 🗄️ DATABASE HANDLES 📄 STATEMENT HANDLES 🔄 TRANSACTION SUPPORT 📊 MULTIPLE RESULT SETS 🧵 MULTITHREADING ⚡ ASYNCHRONOUS QUERIES 📦 INSTALLATION 👤 AUTHORS 🤝 CONTRIBUTIONS ©️ COPYRIGHT 📜 LICENSE 📧 MAILING LIST SUPPORT ℹ️ ADDITIONAL DBI INFORMATION 🐛 BUG REPORTING, ENHANCEMENT/FEATURE REQUESTS