1DBD::mysql(3) User Contributed Perl Documentation DBD::mysql(3)
2
6 DBD::mysql - MySQL driver for the Perl5 Database Interface (DBI)
7
9 use DBI;
10 my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
12 my $dbh = DBI->connect($dsn, $user, $password);
13 my $sth = $dbh->prepare(
15 'SELECT id, first_name, last_name FROM authors WHERE last_name = ?')
16 or die "prepare statement failed: $dbh->errstr()";
17 $sth->execute('Eggers') or die "execution failed: $dbh->errstr()";
18 print $sth->rows . " rows found.\n";
19 while (my $ref = $sth->fetchrow_hashref()) {
20 print "Found a row: id = $ref->{'id'}, fn = $ref->{'first_name'}\n";
21 }
22 $sth->finish;
23
25 #!/usr/bin/perl
26 use strict;
28 use warnings;
29 use DBI;
30 # Connect to the database.
32 my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost",
33 "joe", "joe's password",
34 {'RaiseError' => 1});
35 # Drop table 'foo'. This may fail, if 'foo' doesn't exist
37 # Thus we put an eval around it.
38 eval { $dbh->do("DROP TABLE foo") };
39 print "Dropping foo failed: $@\n" if $@;
40 # Create a new table 'foo'. This must not fail, thus we don't
42 # catch errors.
43 $dbh->do("CREATE TABLE foo (id INTEGER, name VARCHAR(20))");
44 # INSERT some data into 'foo'. We are using $dbh->quote() for
46 # quoting the name.
47 $dbh->do("INSERT INTO foo VALUES (1, " . $dbh->quote("Tim") . ")");
48 # same thing, but using placeholders (recommended!)
50 $dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen");
51 # now retrieve data from the table.
53 my $sth = $dbh->prepare("SELECT * FROM foo");
54 $sth->execute();
55 while (my $ref = $sth->fetchrow_hashref()) {
56 print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n";
57 }
58 $sth->finish();
59 # Disconnect from the database.
61 $dbh->disconnect();
62
64 DBD::mysql is the Perl5 Database Interface driver for the MySQL
65 database. In other words: DBD::mysql is an interface between the Perl
66 programming language and the MySQL programming API that comes with the
67 MySQL relational database management system. Most functions provided by
68 this programming API are supported. Some rarely used functions are
69 missing, mainly because no-one ever requested them. :-)
70 In what follows we first discuss the use of DBD::mysql, because this is
72 what you will need the most. For installation, see the separate
73 document DBD::mysql::INSTALL. See "EXAMPLE" for a simple example
74 above.
75 From perl you activate the interface with the statement
77 use DBI;
79 After that you can connect to multiple MySQL database servers and send
81 multiple queries to any of them via a simple object oriented interface.
82 Two types of objects are available: database handles and statement
83 handles. Perl returns a database handle to the connect method like so:
84 $dbh = DBI->connect("DBI:mysql:database=$db;host=$host",
86 $user, $password, {RaiseError => 1});
87 Once you have connected to a database, you can execute SQL statements
89 with:
90 my $query = sprintf("INSERT INTO foo VALUES (%d, %s)",
92 $number, $dbh->quote("name"));
93 $dbh->do($query);
94 See DBI for details on the quote and do methods. An alternative
96 approach is
97 $dbh->do("INSERT INTO foo VALUES (?, ?)", undef,
99 $number, $name);
100 in which case the quote method is executed automatically. See also the
102 bind_param method in DBI. See "DATABASE HANDLES" below for more details
103 on database handles.
104 If you want to retrieve results, you need to create a so-called
106 statement handle with:
107 $sth = $dbh->prepare("SELECT * FROM $table");
109 $sth->execute();
110 This statement handle can be used for multiple things. First of all you
112 can retrieve a row of data:
113 my $row = $sth->fetchrow_hashref();
115 If your table has columns ID and NAME, then $row will be hash ref with
117 keys ID and NAME. See "STATEMENT HANDLES" below for more details on
118 statement handles.
119 But now for a more formal approach:
121 Class Methods
123 connect
124 use DBI;
125 $dsn = "DBI:mysql:$database";
127 $dsn = "DBI:mysql:database=$database;host=$hostname";
128 $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
129 $dbh = DBI->connect($dsn, $user, $password);
131 The "database" is not a required attribute, but please note that
133 MySQL has no such thing as a default database. If you don't specify
134 the database at connection time your active database will be null
135 and you'd need to prefix your tables with the database name; i.e.
136 'SELECT * FROM mydb.mytable'.
137 This is similar to the behavior of the mysql command line client.
139 Also, 'SELECT DATABASE()' will return the current database active
140 for the handle.
141 host
143 port
144 The hostname, if not specified or specified as '' or
145 'localhost', will default to a MySQL server running on the
146 local machine using the default for the UNIX socket. To connect
147 to a MySQL server on the local machine via TCP, you must
148 specify the loopback IP address (127.0.0.1) as the host.
149 Should the MySQL server be running on a non-standard port
151 number, you may explicitly state the port number to connect to
152 in the "hostname" argument, by concatenating the hostname and
153 port number together separated by a colon ( ":" ) character or
154 by using the "port" argument.
155 To connect to a MySQL server on localhost using TCP/IP, you
157 must specify the hostname as 127.0.0.1 (with the optional
158 port).
159 When connecting to a MySQL Server with IPv6, a bracketed IPv6
161 address should be used. Example DSN:
162 my $dsn = "DBI:mysql:;host=[1a12:2800:6f2:85::f20:8cf];port=3306";
164 mysql_client_found_rows
166 Enables (TRUE value) or disables (FALSE value) the flag
167 CLIENT_FOUND_ROWS while connecting to the MySQL server. This
168 has a somewhat funny effect: Without mysql_client_found_rows,
169 if you perform a query like
170 UPDATE $table SET id = 1 WHERE id = 1;
172 then the MySQL engine will always return 0, because no rows
174 have changed. With mysql_client_found_rows however, it will
175 return the number of rows that have an id 1, as some people are
176 expecting. (At least for compatibility to other engines.)
177 mysql_compression
179 If your DSN contains the option "mysql_compression=1", then the
180 communication between client and server will be compressed.
181 mysql_connect_timeout
183 If your DSN contains the option "mysql_connect_timeout=##", the
184 connect request to the server will timeout if it has not been
185 successful after the given number of seconds.
186 mysql_write_timeout
188 If your DSN contains the option "mysql_write_timeout=##", the
189 write operation to the server will timeout if it has not been
190 successful after the given number of seconds.
191 mysql_read_timeout
193 If your DSN contains the option "mysql_read_timeout=##", the
194 read operation to the server will timeout if it has not been
195 successful after the given number of seconds.
196 mysql_init_command
198 If your DSN contains the option "mysql_init_command=##", then
199 this SQL statement is executed when connecting to the MySQL
200 server. It is automatically re-executed if reconnection
201 occurs.
202 mysql_skip_secure_auth
204 This option is for older mysql databases that don't have secure
205 auth set.
206 mysql_read_default_file
208 mysql_read_default_group
209 These options can be used to read a config file like
210 /etc/my.cnf or ~/.my.cnf. By default MySQL's C client library
211 doesn't use any config files unlike the client programs (mysql,
212 mysqladmin, ...) that do, but outside of the C client library.
213 Thus you need to explicitly request reading a config file, as
214 in
215 $dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
217 $dbh = DBI->connect($dsn, $user, $password)
218 The option mysql_read_default_group can be used to specify the
220 default group in the config file: Usually this is the client
221 group, but see the following example:
222 [client]
224 host=localhost
225 [perl]
227 host=perlhost
228 (Note the order of the entries! The example won't work, if you
230 reverse the [client] and [perl] sections!)
231 If you read this config file, then you'll be typically
233 connected to localhost. However, by using
234 $dsn = "DBI:mysql:test;mysql_read_default_group=perl;"
236 . "mysql_read_default_file=/home/joe/my.cnf";
237 $dbh = DBI->connect($dsn, $user, $password);
238 you'll be connected to perlhost. Note that if you specify a
240 default group and do not specify a file, then the default
241 config files will all be read. See the documentation of the C
242 function mysql_options() for details.
243 mysql_socket
245 It is possible to choose the Unix socket that is used for
246 connecting to the server. This is done, for example, with
247 mysql_socket=/dev/mysql
249 Usually there's no need for this option, unless you are using
251 another location for the socket than that built into the
252 client.
253 mysql_ssl
255 A true value turns on the CLIENT_SSL flag when connecting to
256 the MySQL server and enforce SSL encryption. A false value
257 (which is default) disable SSL encryption with the MySQL
258 server.
259 When enabling SSL encryption you should set also other SSL
261 options, at least mysql_ssl_ca_file or mysql_ssl_ca_path.
262 mysql_ssl=1 mysql_ssl_verify_server_cert=1 mysql_ssl_ca_file=/path/to/ca_cert.pem
264 This means that your communication with the server will be
266 encrypted.
267 Please note that this can only work if you enabled SSL when
269 compiling DBD::mysql; this is the default starting version
270 4.034. See DBD::mysql::INSTALL for more details.
271 mysql_ssl_ca_file
273 The path to a file in PEM format that contains a list of
274 trusted SSL certificate authorities.
275 When set MySQL server certificate is checked that it is signed
277 by some CA certificate in the list. Common Name value is not
278 verified unless "mysql_ssl_verify_server_cert" is enabled.
279 mysql_ssl_ca_path
281 The path to a directory that contains trusted SSL certificate
282 authority certificates in PEM format.
283 When set MySQL server certificate is checked that it is signed
285 by some CA certificate in the list. Common Name value is not
286 verified unless "mysql_ssl_verify_server_cert" is enabled.
287 Please note that this option is supported only if your MySQL
289 client was compiled with OpenSSL library, and not with default
290 yaSSL library.
291 mysql_ssl_verify_server_cert
293 Checks the server's Common Name value in the certificate that
294 the server sends to the client. The client verifies that name
295 against the host name the client uses for connecting to the
296 server, and the connection fails if there is a mismatch. For
297 encrypted connections, this option helps prevent man-in-the-
298 middle attacks.
299 Verification of the host name is disabled by default.
301 mysql_ssl_client_key
303 The name of the SSL key file in PEM format to use for
304 establishing a secure connection.
305 mysql_ssl_client_cert
307 The name of the SSL certificate file in PEM format to use for
308 establishing a secure connection.
309 mysql_ssl_cipher
311 A list of permissible ciphers to use for connection encryption.
312 If no cipher in the list is supported, encrypted connections
313 will not work.
314 mysql_ssl_cipher=AES128-SHA
316 mysql_ssl_cipher=DHE-RSA-AES256-SHA:AES128-SHA
317 mysql_ssl_optional
319 Setting "mysql_ssl_optional" to true disables strict SSL
320 enforcement and makes SSL connection optional. This option
321 opens security hole for man-in-the-middle attacks. Default
322 value is false which means that "mysql_ssl" set to true enforce
323 SSL encryption.
324 This option was introduced in 4.043 version of DBD::mysql. Due
326 to The BACKRONYM <http://backronym.fail/> and The Riddle
327 <http://riddle.link/> vulnerabilities in libmysqlclient
328 library, enforcement of SSL encryption was not possbile and
329 therefore "mysql_ssl_optional=1" was effectively set for all
330 DBD::mysql versions prior to 4.043. Starting with 4.043,
331 DBD::mysql with "mysql_ssl=1" could refuse connection to MySQL
332 server if underlaying libmysqlclient library is vulnerable.
333 Option "mysql_ssl_optional" can be used to make SSL connection
334 vulnerable.
335 mysql_server_pubkey
337 Path to the RSA public key of the server. This is used for the
338 sha256_password and caching_sha2_password authentication
339 plugins.
340 mysql_get_server_pubkey
342 Setting "mysql_get_server_pubkey" to true requests the public
343 RSA key of the server.
344 mysql_local_infile
346 The LOCAL capability for LOAD DATA may be disabled in the MySQL
347 client library by default. If your DSN contains the option
348 "mysql_local_infile=1", LOAD DATA LOCAL will be enabled.
349 (However, this option is *ineffective* if the server has also
350 been configured to disallow LOCAL.)
351 mysql_multi_statements
353 Support for multiple statements separated by a semicolon (;)
354 may be enabled by using this option. Enabling this option may
355 cause problems if server-side prepared statements are also
356 enabled.
357 mysql_server_prepare
359 This option is used to enable server side prepared statements.
360 To use server side prepared statements, all you need to do is
362 set the variable mysql_server_prepare in the connect:
363 $dbh = DBI->connect(
365 "DBI:mysql:database=test;host=localhost;mysql_server_prepare=1",
366 "",
367 "",
368 { RaiseError => 1, AutoCommit => 1 }
369 );
370 or:
372 $dbh = DBI->connect(
374 "DBI:mysql:database=test;host=localhost",
375 "",
376 "",
377 { RaiseError => 1, AutoCommit => 1, mysql_server_prepare => 1 }
378 );
379 There are many benefits to using server side prepare
381 statements, mostly if you are performing many inserts because
382 of that fact that a single statement is prepared to accept
383 multiple insert values.
384 To make sure that the 'make test' step tests whether server
386 prepare works, you just need to export the env variable
387 MYSQL_SERVER_PREPARE:
388 export MYSQL_SERVER_PREPARE=1
390 Please note that mysql server cannot prepare or execute some
392 prepared statements. In this case DBD::mysql fallbacks to
393 normal non-prepared statement and tries again.
394 mysql_server_prepare_disable_fallback
396 This option disable fallback to normal non-prepared statement
397 when mysql server does not support execution of current
398 statement as prepared.
399 Useful when you want to be sure that statement is going to be
401 executed as server side prepared. Error message and code in
402 case of failure is propagated back to DBI.
403 mysql_embedded_options
405 The option <mysql_embedded_options> can be used to pass
406 'command-line' options to embedded server.
407 Example:
409 use DBI;
411 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_options=--help,--verbose";
412 $dbh = DBI->connect($testdsn,"a","b");
413 This would cause the command line help to the embedded MySQL
415 server library to be printed.
416 mysql_embedded_groups
418 The option <mysql_embedded_groups> can be used to specify the
419 groups in the config file(my.cnf) which will be used to get
420 options for embedded server. If not specified [server] and
421 [embedded] groups will be used.
422 Example:
424 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_groups=embedded_server,common";
426 mysql_conn_attrs
428 The option <mysql_conn_attrs> is a hash of attribute names and
429 values which can be used to send custom connection attributes
430 to the server. Some attributes like '_os', '_platform',
431 '_client_name' and '_client_version' are added by
432 libmysqlclient and 'program_name' is added by DBD::mysql.
433 You can then later read these attributes from the performance
435 schema tables which can be quite helpful for profiling your
436 database or creating statistics. You'll have to use a MySQL
437 5.6 server and libmysqlclient or newer to leverage this
438 feature.
439 my $dbh= DBI->connect($dsn, $user, $password,
441 { AutoCommit => 0,
442 mysql_conn_attrs => {
443 foo => 'bar',
444 wiz => 'bang'
445 },
446 });
447 Now you can select the results from the performance schema
449 tables. You can do this in the same session, but also
450 afterwards. It can be very useful to answer questions like
451 'which script sent this query?'.
452 my $results = $dbh->selectall_hashref(
454 'SELECT * FROM performance_schema.session_connect_attrs',
455 'ATTR_NAME'
456 );
457 This returns:
459 $result = {
461 'foo' => {
462 'ATTR_VALUE' => 'bar',
463 'PROCESSLIST_ID' => '3',
464 'ATTR_NAME' => 'foo',
465 'ORDINAL_POSITION' => '6'
466 },
467 'wiz' => {
468 'ATTR_VALUE' => 'bang',
469 'PROCESSLIST_ID' => '3',
470 'ATTR_NAME' => 'wiz',
471 'ORDINAL_POSITION' => '3'
472 },
473 'program_name' => {
474 'ATTR_VALUE' => './foo.pl',
475 'PROCESSLIST_ID' => '3',
476 'ATTR_NAME' => 'program_name',
477 'ORDINAL_POSITION' => '5'
478 },
479 '_client_name' => {
480 'ATTR_VALUE' => 'libmysql',
481 'PROCESSLIST_ID' => '3',
482 'ATTR_NAME' => '_client_name',
483 'ORDINAL_POSITION' => '1'
484 },
485 '_client_version' => {
486 'ATTR_VALUE' => '5.6.24',
487 'PROCESSLIST_ID' => '3',
488 'ATTR_NAME' => '_client_version',
489 'ORDINAL_POSITION' => '7'
490 },
491 '_os' => {
492 'ATTR_VALUE' => 'osx10.8',
493 'PROCESSLIST_ID' => '3',
494 'ATTR_NAME' => '_os',
495 'ORDINAL_POSITION' => '0'
496 },
497 '_pid' => {
498 'ATTR_VALUE' => '59860',
499 'PROCESSLIST_ID' => '3',
500 'ATTR_NAME' => '_pid',
501 'ORDINAL_POSITION' => '2'
502 },
503 '_platform' => {
504 'ATTR_VALUE' => 'x86_64',
505 'PROCESSLIST_ID' => '3',
506 'ATTR_NAME' => '_platform',
507 'ORDINAL_POSITION' => '4'
508 }
509 };
510 Private MetaData Methods
512 ListDBs
513 my $drh = DBI->install_driver("mysql");
514 @dbs = $drh->func("$hostname:$port", '_ListDBs');
515 @dbs = $drh->func($hostname, $port, '_ListDBs');
516 @dbs = $dbh->func('_ListDBs');
517 Returns a list of all databases managed by the MySQL server running
519 on $hostname, port $port. This is a legacy method. Instead, you
520 should use the portable method
521 @dbs = DBI->data_sources("mysql");
523
525 The DBD::mysql driver supports the following attributes of database
526 handles (read only):
527 $errno = $dbh->{'mysql_errno'};
529 $error = $dbh->{'mysql_error'};
530 $info = $dbh->{'mysql_hostinfo'};
531 $info = $dbh->{'mysql_info'};
532 $insertid = $dbh->{'mysql_insertid'};
533 $info = $dbh->{'mysql_protoinfo'};
534 $info = $dbh->{'mysql_serverinfo'};
535 $info = $dbh->{'mysql_stat'};
536 $threadId = $dbh->{'mysql_thread_id'};
537 These correspond to mysql_errno(), mysql_error(),
539 mysql_get_host_info(), mysql_info(), mysql_insert_id(),
540 mysql_get_proto_info(), mysql_get_server_info(), mysql_stat() and
541 mysql_thread_id(), respectively.
542 mysql_clientinfo
544 List information of the MySQL client library that DBD::mysql was
545 built against:
546 print "$dbh->{mysql_clientinfo}\n";
548 5.2.0-MariaDB
550 mysql_clientversion
552 print "$dbh->{mysql_clientversion}\n";
553 50200
555 mysql_serverversion
557 print "$dbh->{mysql_serverversion}\n";
558 50200
560 mysql_dbd_stats
562 $info_hashref = $dbh->{mysql_dbd_stats};
563 DBD::mysql keeps track of some statistics in the mysql_dbd_stats
565 attribute. The following stats are being maintained:
566 auto_reconnects_ok
568 The number of times that DBD::mysql successfully reconnected
569 to the mysql server.
570 auto_reconnects_failed
572 The number of times that DBD::mysql tried to reconnect to
573 mysql but failed.
574 The DBD::mysql driver also supports the following attributes of
576 database handles (read/write):
577 mysql_auto_reconnect
579 This attribute determines whether DBD::mysql will automatically
580 reconnect to mysql if the connection be lost. This feature defaults
581 to off; however, if either the GATEWAY_INTERFACE or MOD_PERL
582 environment variable is set, DBD::mysql will turn
583 mysql_auto_reconnect on. Setting mysql_auto_reconnect to on is not
584 advised if 'lock tables' is used because if DBD::mysql reconnect to
585 mysql all table locks will be lost. This attribute is ignored when
586 AutoCommit is turned off, and when AutoCommit is turned off,
587 DBD::mysql will not automatically reconnect to the server.
588 It is also possible to set the default value of the
590 "mysql_auto_reconnect" attribute for the $dbh by passing it in the
591 "\%attr" hash for "DBI-"connect>.
592 $dbh->{mysql_auto_reconnect} = 1;
594 or
596 my $dbh = DBI->connect($dsn, $user, $password, {
598 mysql_auto_reconnect => 1,
599 });
600 Note that if you are using a module or framework that performs
602 reconnections for you (for example DBIx::Connector in fixup mode),
603 this value must be set to 0.
604 mysql_use_result
606 This attribute forces the driver to use mysql_use_result rather
607 than mysql_store_result. The former is faster and less memory
608 consuming, but tends to block other processes. mysql_store_result
609 is the default due to that fact storing the result is expected
610 behavior with most applications.
611 It is possible to set the default value of the "mysql_use_result"
613 attribute for the $dbh via the DSN:
614 $dbh = DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
616 You can also set it after creation of the database handle:
618 $dbh->{mysql_use_result} = 0; # disable
620 $dbh->{mysql_use_result} = 1; # enable
621 You can also set or unset the "mysql_use_result" setting on your
623 statement handle, when creating the statement handle or after it
624 has been created. See "STATEMENT HANDLES".
625 mysql_enable_utf8
627 This attribute determines whether DBD::mysql should assume strings
628 stored in the database are utf8. This feature defaults to off.
629 When set, a data retrieved from a textual column type (char,
631 varchar, etc) will have the UTF-8 flag turned on if necessary.
632 This enables character semantics on that string. You will also
633 need to ensure that your database / table / column is configured to
634 use UTF8. See for more information the chapter on character set
635 support in the MySQL manual:
636 <http://dev.mysql.com/doc/refman/5.7/en/charset.html>
637 Additionally, turning on this flag tells MySQL that incoming data
639 should be treated as UTF-8. This will only take effect if used as
640 part of the call to connect(). If you turn the flag on after
641 connecting, you will need to issue the command "SET NAMES utf8" to
642 get the same effect.
643 mysql_enable_utf8mb4
645 This is similar to mysql_enable_utf8, but is capable of handling
646 4-byte UTF-8 characters.
647 mysql_bind_type_guessing
649 This attribute causes the driver (emulated prepare statements) to
650 attempt to guess if a value being bound is a numeric value, and if
651 so, doesn't quote the value. This was created by Dragonchild and
652 is one way to deal with the performance issue of using quotes in a
653 statement that is inserting or updating a large numeric value. This
654 was previously called "unsafe_bind_type_guessing" because it is
655 experimental. I have successfully run the full test suite with this
656 option turned on, the name can now be simply
657 "mysql_bind_type_guessing".
658 CAVEAT: Even though you can insert an integer value into a
660 character column, if this column is indexed, if you query that
661 column with the integer value not being quoted, it will not use the
662 index:
663 MariaDB [test]> explain select * from test where value0 = '3' \G
665 *************************** 1. row ***************************
666 id: 1
667 select_type: SIMPLE
668 table: test
669 type: ref
670 possible_keys: value0
671 key: value0
672 key_len: 13
673 ref: const
674 rows: 1
675 Extra: Using index condition
676 1 row in set (0.00 sec)
677 MariaDB [test]> explain select * from test where value0 = 3
679 -> \G
680 *************************** 1. row ***************************
681 id: 1
682 select_type: SIMPLE
683 table: test
684 type: ALL
685 possible_keys: value0
686 key: NULL
687 key_len: NULL
688 ref: NULL
689 rows: 6
690 Extra: Using where
691 1 row in set (0.00 sec)
692 See bug: https://rt.cpan.org/Ticket/Display.html?id=43822
694 "mysql_bind_type_guessing" can be turned on via
696 - through DSN
698 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
700 { mysql_bind_type_guessing => 1})
701 - OR after handle creation
703 $dbh->{mysql_bind_type_guessing} = 1;
705 mysql_bind_comment_placeholders
707 This attribute causes the driver (emulated prepare statements) will
708 cause any placeholders in comments to be bound. This is not correct
709 prepared statement behavior, but some developers have come to
710 depend on this behavior, so I have made it available in 4.015
711 mysql_no_autocommit_cmd
713 This attribute causes the driver to not issue 'set autocommit'
714 either through explicit or using mysql_autocommit(). This is
715 particularly useful in the case of using MySQL Proxy.
716 See the bug report:
718 https://rt.cpan.org/Public/Bug/Display.html?id=46308
720 "mysql_no_autocommit_cmd" can be turned on when creating the
722 database handle:
723 my $dbh = DBI->connect('DBI:mysql:test', 'username', 'pass',
725 { mysql_no_autocommit_cmd => 1});
726 or using an existing database handle:
728 $dbh->{mysql_no_autocommit_cmd} = 1;
730 ping
732 This can be used to send a ping to the server.
733 $rc = $dbh->ping();
735
737 The statement handles of DBD::mysql support a number of attributes. You
738 access these by using, for example,
739 my $numFields = $sth->{NUM_OF_FIELDS};
741 Note, that most attributes are valid only after a successful execute.
743 An "undef" value will returned otherwise. The most important exception
744 is the "mysql_use_result" attribute, which forces the driver to use
745 mysql_use_result rather than mysql_store_result. The former is faster
746 and less memory consuming, but tends to block other processes. (That's
747 why mysql_store_result is the default.)
748 To set the "mysql_use_result" attribute, use either of the following:
750 my $sth = $dbh->prepare("QUERY", { mysql_use_result => 1});
752 or
754 my $sth = $dbh->prepare($sql);
756 $sth->{mysql_use_result} = 1;
757 Column dependent attributes, for example NAME, the column names, are
759 returned as a reference to an array. The array indices are
760 corresponding to the indices of the arrays returned by fetchrow and
761 similar methods. For example the following code will print a header of
762 table names together with all rows:
763 my $sth = $dbh->prepare("SELECT * FROM $table") ||
765 die "Error:" . $dbh->errstr . "\n";
766 $sth->execute || die "Error:" . $sth->errstr . "\n";
768 my $names = $sth->{NAME};
770 my $numFields = $sth->{'NUM_OF_FIELDS'} - 1;
771 for my $i ( 0..$numFields ) {
772 printf("%s%s", $i ? "," : "", $$names[$i]);
773 }
774 print "\n";
775 while (my $ref = $sth->fetchrow_arrayref) {
776 for my $i ( 0..$numFields ) {
777 printf("%s%s", $i ? "," : "", $$ref[$i]);
778 }
779 print "\n";
780 }
781 For portable applications you should restrict yourself to attributes
783 with capitalized or mixed case names. Lower case attribute names are
784 private to DBD::mysql. The attribute list includes:
785 ChopBlanks
787 this attribute determines whether a fetchrow will chop preceding
788 and trailing blanks off the column values. Chopping blanks does not
789 have impact on the max_length attribute.
790 mysql_gtids
792 Returns GTID(s) if GTID session tracking is ensabled in the server
793 via session_track_gtids.
794 mysql_insertid
796 If the statement you executed performs an INSERT, and there is an
797 AUTO_INCREMENT column in the table you inserted in, this attribute
798 holds the value stored into the AUTO_INCREMENT column, if that
799 value is automatically generated, by storing NULL or 0 or was
800 specified as an explicit value.
801 Typically, you'd access the value via $sth->{mysql_insertid}. The
803 value can also be accessed via $dbh->{mysql_insertid} but this can
804 easily produce incorrect results in case one database handle is
805 shared.
806 mysql_is_blob
808 Reference to an array of boolean values; TRUE indicates, that the
809 respective column is a blob. This attribute is valid for MySQL
810 only.
811 mysql_is_key
813 Reference to an array of boolean values; TRUE indicates, that the
814 respective column is a key. This is valid for MySQL only.
815 mysql_is_num
817 Reference to an array of boolean values; TRUE indicates, that the
818 respective column contains numeric values.
819 mysql_is_pri_key
821 Reference to an array of boolean values; TRUE indicates, that the
822 respective column is a primary key.
823 mysql_is_auto_increment
825 Reference to an array of boolean values; TRUE indicates that the
826 respective column is an AUTO_INCREMENT column. This is only valid
827 for MySQL.
828 mysql_length
830 mysql_max_length
831 A reference to an array of maximum column sizes. The max_length is
832 the maximum physically present in the result table, length gives
833 the theoretically possible maximum. max_length is valid for MySQL
834 only.
835 NAME
837 A reference to an array of column names.
838 NULLABLE
840 A reference to an array of boolean values; TRUE indicates that this
841 column may contain NULL's.
842 NUM_OF_FIELDS
844 Number of fields returned by a SELECT or LISTFIELDS statement. You
845 may use this for checking whether a statement returned a result: A
846 zero value indicates a non-SELECT statement like INSERT, DELETE or
847 UPDATE.
848 mysql_table
850 A reference to an array of table names, useful in a JOIN result.
851 TYPE
853 A reference to an array of column types. The engine's native column
854 types are mapped to portable types like DBI::SQL_INTEGER() or
855 DBI::SQL_VARCHAR(), as good as possible. Not all native types have
856 a meaningful equivalent, for example
857 DBD::mysql::FIELD_TYPE_INTERVAL is mapped to DBI::SQL_VARCHAR().
858 If you need the native column types, use mysql_type. See below.
859 mysql_type
861 A reference to an array of MySQL's native column types, for example
862 DBD::mysql::FIELD_TYPE_SHORT() or DBD::mysql::FIELD_TYPE_STRING().
863 Use the TYPE attribute, if you want portable types like
864 DBI::SQL_SMALLINT() or DBI::SQL_VARCHAR().
865 mysql_type_name
867 Similar to mysql, but type names and not numbers are returned.
868 Whenever possible, the ANSI SQL name is preferred.
869 mysql_warning_count
871 The number of warnings generated during execution of the SQL
872 statement. This attribute is available on both statement handles
873 and database handles.
874
876 The transaction support works as follows:
877 • By default AutoCommit mode is on, following the DBI specifications.
879 • If you execute
881 $dbh->{AutoCommit} = 0;
883 or
885 $dbh->{AutoCommit} = 1;
887 then the driver will set the MySQL server variable autocommit to 0
889 or 1, respectively. Switching from 0 to 1 will also issue a COMMIT,
890 following the DBI specifications.
891 • The methods
893 $dbh->rollback();
895 $dbh->commit();
896 will issue the commands ROLLBACK and COMMIT, respectively. A
898 ROLLBACK will also be issued if AutoCommit mode is off and the
899 database handles DESTROY method is called. Again, this is following
900 the DBI specifications.
901 Given the above, you should note the following:
903 • You should never change the server variable autocommit manually,
905 unless you are ignoring DBI's transaction support.
906 • Switching AutoCommit mode from on to off or vice versa may fail.
908 You should always check for errors when changing AutoCommit mode.
909 The suggested way of doing so is using the DBI flag RaiseError. If
910 you don't like RaiseError, you have to use code like the following:
911 $dbh->{AutoCommit} = 0;
913 if ($dbh->{AutoCommit}) {
914 # An error occurred!
915 }
916 • If you detect an error while changing the AutoCommit mode, you
918 should no longer use the database handle. In other words, you
919 should disconnect and reconnect again, because the transaction mode
920 is unpredictable. Alternatively you may verify the transaction mode
921 by checking the value of the server variable autocommit. However,
922 such behaviour isn't portable.
923 • DBD::mysql has a "reconnect" feature that handles the so-called
925 MySQL "morning bug": If the server has disconnected, most probably
926 due to a timeout, then by default the driver will reconnect and
927 attempt to execute the same SQL statement again. However, this
928 behaviour is disabled when AutoCommit is off: Otherwise the
929 transaction state would be completely unpredictable after a
930 reconnect.
931 • The "reconnect" feature of DBD::mysql can be toggled by using the
933 mysql_auto_reconnect attribute. This behaviour should be turned off
934 in code that uses LOCK TABLE because if the database server time
935 out and DBD::mysql reconnect, table locks will be lost without any
936 indication of such loss.
937
939 DBD::mysql supports multiple result sets, thanks to Guy Harrison!
940 The basic usage of multiple result sets is
942 do
944 {
945 while (@row = $sth->fetchrow_array())
946 {
947 do stuff;
948 }
949 } while ($sth->more_results)
950 An example would be:
952 $dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
954 $dbh->do("create procedure someproc() deterministic
956 begin
957 declare a,b,c,d int;
958 set a=1;
959 set b=2;
960 set c=3;
961 set d=4;
962 select a, b, c, d;
963 select d, c, b, a;
964 select b, a, c, d;
965 select c, b, d, a;
966 end") or print $DBI::errstr;
967 $sth=$dbh->prepare('call someproc()') ||
969 die $DBI::err.": ".$DBI::errstr;
970 $sth->execute || die DBI::err.": ".$DBI::errstr; $rowset=0;
972 do {
973 print "\nRowset ".++$i."\n---------------------------------------\n\n";
974 foreach $colno (0..$sth->{NUM_OF_FIELDS}-1) {
975 print $sth->{NAME}->[$colno]."\t";
976 }
977 print "\n";
978 while (@row= $sth->fetchrow_array()) {
979 foreach $field (0..$#row) {
980 print $row[$field]."\t";
981 }
982 print "\n";
983 }
984 } until (!$sth->more_results)
985 Issues with multiple result sets
987 Please be aware there could be issues if your result sets are "jagged",
988 meaning the number of columns of your results vary. Varying numbers of
989 columns could result in your script crashing.
990
992 The multithreading capabilities of DBD::mysql depend completely on the
993 underlying C libraries. The modules are working with handle data only,
994 no global variables are accessed or (to the best of my knowledge)
995 thread unsafe functions are called. Thus DBD::mysql is believed to be
996 completely thread safe, if the C libraries are thread safe and you
997 don't share handles among threads.
998 The obvious question is: Are the C libraries thread safe? In the case
1000 of MySQL the answer is yes, since MySQL 5.5 it is.
1001
1003 You can make a single asynchronous query per MySQL connection; this
1004 allows you to submit a long-running query to the server and have an
1005 event loop inform you when it's ready. An asynchronous query is
1006 started by either setting the 'async' attribute to a true value in the
1007 "do" in DBI method, or in the "prepare" in DBI method. Statements
1008 created with 'async' set to true in prepare always run their queries
1009 asynchronously when "execute" in DBI is called. The driver also offers
1010 three additional methods: "mysql_async_result", "mysql_async_ready",
1011 and "mysql_fd". "mysql_async_result" returns what do or execute would
1012 have; that is, the number of rows affected. "mysql_async_ready"
1013 returns true if "mysql_async_result" will not block, and zero
1014 otherwise. They both return "undef" if that handle was not created
1015 with 'async' set to true or if an asynchronous query was not started
1016 yet. "mysql_fd" returns the file descriptor number for the MySQL
1017 connection; you can use this in an event loop.
1018 Here's an example of how to use the asynchronous query interface:
1020 use feature 'say';
1022 $dbh->do('SELECT SLEEP(10)', { async => 1 });
1023 until($dbh->mysql_async_ready) {
1024 say 'not ready yet!';
1025 sleep 1;
1026 }
1027 my $rows = $dbh->mysql_async_result;
1028
1030 See DBD::mysql::INSTALL.
1031
1033 Originally, there was a non-DBI driver, Mysql, which was much like PHP
1034 drivers such as mysql and mysqli. The Mysql module was originally
1035 written by Andreas König <koenig@kulturbox.de> who still, to this day,
1036 contributes patches to DBD::mysql. An emulated version of Mysql was
1037 provided to DBD::mysql from Jochen Wiedmann, but eventually deprecated
1038 as it was another bundle of code to maintain.
1039 The first incarnation of DBD::mysql was developed by Alligator
1041 Descartes, who was also aided and abetted by Gary Shea, Andreas König
1042 and Tim Bunce.
1043 The current incarnation of DBD::mysql was written by Jochen Wiedmann,
1045 then numerous changes and bug-fixes were added by Rudy Lippan. Next,
1046 prepared statement support was added by Patrick Galbraith and Alexy
1047 Stroganov (who also solely added embedded server support).
1048 For the past nine years DBD::mysql has been maintained by Patrick
1050 Galbraith (patg@patg.net), and recently with the great help of Michiel
1051 Beijen (michiel.beijen@gmail.com), along with the entire community of
1052 Perl developers who keep sending patches to help continue improving
1053 DBD::mysql
1054
1056 Anyone who desires to contribute to this project is encouraged to do
1057 so. Currently, the source code for this project can be found at
1058 Github:
1059 <https://github.com/perl5-dbi/DBD-mysql/>
1061 Either fork this repository and produce a branch with your changeset
1063 that the maintainer can merge to his tree, or create a diff with git.
1064 The maintainer is more than glad to take contributions from the
1065 community as many features and fixes from DBD::mysql have come from the
1066 community.
1067
1069 This module is
1070 • Large Portions Copyright (c) 2004-2013 Patrick Galbraith
1072 • Large Portions Copyright (c) 2004-2006 Alexey Stroganov
1074 • Large Portions Copyright (c) 2003-2005 Rudolf Lippan
1076 • Large Portions Copyright (c) 1997-2003 Jochen Wiedmann, with code
1078 portions
1079 • Copyright (c)1994-1997 their original authors
1081
1083 This module is released under the same license as Perl itself. See
1084 <http://www.perl.com/perl/misc/Artistic.html> for details.
1085
1087 This module is maintained and supported on a mailing list, dbi-users.
1088 To subscribe to this list, send an email to
1090 dbi-users-subscribe@perl.org
1092 Mailing list archives are at
1094 <http://groups.google.com/group/perl.dbi.users?hl=en&lr=>
1096
1098 Additional information on the DBI project can be found on the World
1099 Wide Web at the following URL:
1100 <http://dbi.perl.org>
1102 where documentation, pointers to the mailing lists and mailing list
1104 archives and pointers to the most current versions of the modules can
1105 be used.
1106 Information on the DBI interface itself can be gained by typing:
1108 perldoc DBI
1110 Information on DBD::mysql specifically can be gained by typing:
1112 perldoc DBD::mysql
1114 (this will display the document you're currently reading)
1116
1118 Please report bugs, including all the information needed such as
1119 DBD::mysql version, MySQL version, OS type/version, etc to this link:
1120 <https://rt.cpan.org/Dist/Display.html?Name=DBD-mysql>
1122 Note: until recently, MySQL/Sun/Oracle responded to bugs and assisted
1124 in fixing bugs which many thanks should be given for their help! This
1125 driver is outside the realm of the numerous components they support,
1126 and the maintainer and community solely support DBD::mysql
1127perl v5.34.0 2021-07-22 DBD::mysql(3)