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_local_infile
337 The LOCAL capability for LOAD DATA may be disabled in the MySQL
338 client library by default. If your DSN contains the option
339 "mysql_local_infile=1", LOAD DATA LOCAL will be enabled.
340 (However, this option is *ineffective* if the server has also
341 been configured to disallow LOCAL.)
342 mysql_multi_statements
344 Support for multiple statements separated by a semicolon (;)
345 may be enabled by using this option. Enabling this option may
346 cause problems if server-side prepared statements are also
347 enabled.
348 mysql_server_prepare
350 This option is used to enable server side prepared statements.
351 To use server side prepared statements, all you need to do is
353 set the variable mysql_server_prepare in the connect:
354 $dbh = DBI->connect(
356 "DBI:mysql:database=test;host=localhost;mysql_server_prepare=1",
357 "",
358 "",
359 { RaiseError => 1, AutoCommit => 1 }
360 );
361 or:
363 $dbh = DBI->connect(
365 "DBI:mysql:database=test;host=localhost",
366 "",
367 "",
368 { RaiseError => 1, AutoCommit => 1, mysql_server_prepare => 1 }
369 );
370 There are many benefits to using server side prepare
372 statements, mostly if you are performing many inserts because
373 of that fact that a single statement is prepared to accept
374 multiple insert values.
375 To make sure that the 'make test' step tests whether server
377 prepare works, you just need to export the env variable
378 MYSQL_SERVER_PREPARE:
379 export MYSQL_SERVER_PREPARE=1
381 Please note that mysql server cannot prepare or execute some
383 prepared statements. In this case DBD::mysql fallbacks to
384 normal non-prepared statement and tries again.
385 mysql_server_prepare_disable_fallback
387 This option disable fallback to normal non-prepared statement
388 when mysql server does not support execution of current
389 statement as prepared.
390 Useful when you want to be sure that statement is going to be
392 executed as server side prepared. Error message and code in
393 case of failure is propagated back to DBI.
394 mysql_embedded_options
396 The option <mysql_embedded_options> can be used to pass
397 'command-line' options to embedded server.
398 Example:
400 use DBI;
402 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_options=--help,--verbose";
403 $dbh = DBI->connect($testdsn,"a","b");
404 This would cause the command line help to the embedded MySQL
406 server library to be printed.
407 mysql_embedded_groups
409 The option <mysql_embedded_groups> can be used to specify the
410 groups in the config file(my.cnf) which will be used to get
411 options for embedded server. If not specified [server] and
412 [embedded] groups will be used.
413 Example:
415 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_groups=embedded_server,common";
417 mysql_conn_attrs
419 The option <mysql_conn_attrs> is a hash of attribute names and
420 values which can be used to send custom connection attributes
421 to the server. Some attributes like '_os', '_platform',
422 '_client_name' and '_client_version' are added by
423 libmysqlclient and 'program_name' is added by DBD::mysql.
424 You can then later read these attributes from the performance
426 schema tables which can be quite helpful for profiling your
427 database or creating statistics. You'll have to use a MySQL
428 5.6 server and libmysqlclient or newer to leverage this
429 feature.
430 my $dbh= DBI->connect($dsn, $user, $password,
432 { AutoCommit => 0,
433 mysql_conn_attrs => {
434 foo => 'bar',
435 wiz => 'bang'
436 },
437 });
438 Now you can select the results from the performance schema
440 tables. You can do this in the same session, but also
441 afterwards. It can be very useful to answer questions like
442 'which script sent this query?'.
443 my $results = $dbh->selectall_hashref(
445 'SELECT * FROM performance_schema.session_connect_attrs',
446 'ATTR_NAME'
447 );
448 This returns:
450 $result = {
452 'foo' => {
453 'ATTR_VALUE' => 'bar',
454 'PROCESSLIST_ID' => '3',
455 'ATTR_NAME' => 'foo',
456 'ORDINAL_POSITION' => '6'
457 },
458 'wiz' => {
459 'ATTR_VALUE' => 'bang',
460 'PROCESSLIST_ID' => '3',
461 'ATTR_NAME' => 'wiz',
462 'ORDINAL_POSITION' => '3'
463 },
464 'program_name' => {
465 'ATTR_VALUE' => './foo.pl',
466 'PROCESSLIST_ID' => '3',
467 'ATTR_NAME' => 'program_name',
468 'ORDINAL_POSITION' => '5'
469 },
470 '_client_name' => {
471 'ATTR_VALUE' => 'libmysql',
472 'PROCESSLIST_ID' => '3',
473 'ATTR_NAME' => '_client_name',
474 'ORDINAL_POSITION' => '1'
475 },
476 '_client_version' => {
477 'ATTR_VALUE' => '5.6.24',
478 'PROCESSLIST_ID' => '3',
479 'ATTR_NAME' => '_client_version',
480 'ORDINAL_POSITION' => '7'
481 },
482 '_os' => {
483 'ATTR_VALUE' => 'osx10.8',
484 'PROCESSLIST_ID' => '3',
485 'ATTR_NAME' => '_os',
486 'ORDINAL_POSITION' => '0'
487 },
488 '_pid' => {
489 'ATTR_VALUE' => '59860',
490 'PROCESSLIST_ID' => '3',
491 'ATTR_NAME' => '_pid',
492 'ORDINAL_POSITION' => '2'
493 },
494 '_platform' => {
495 'ATTR_VALUE' => 'x86_64',
496 'PROCESSLIST_ID' => '3',
497 'ATTR_NAME' => '_platform',
498 'ORDINAL_POSITION' => '4'
499 }
500 };
501 Private MetaData Methods
503 ListDBs
504 my $drh = DBI->install_driver("mysql");
505 @dbs = $drh->func("$hostname:$port", '_ListDBs');
506 @dbs = $drh->func($hostname, $port, '_ListDBs');
507 @dbs = $dbh->func('_ListDBs');
508 Returns a list of all databases managed by the MySQL server running
510 on $hostname, port $port. This is a legacy method. Instead, you
511 should use the portable method
512 @dbs = DBI->data_sources("mysql");
514
516 The DBD::mysql driver supports the following attributes of database
517 handles (read only):
518 $errno = $dbh->{'mysql_errno'};
520 $error = $dbh->{'mysql_error'};
521 $info = $dbh->{'mysql_hostinfo'};
522 $info = $dbh->{'mysql_info'};
523 $insertid = $dbh->{'mysql_insertid'};
524 $info = $dbh->{'mysql_protoinfo'};
525 $info = $dbh->{'mysql_serverinfo'};
526 $info = $dbh->{'mysql_stat'};
527 $threadId = $dbh->{'mysql_thread_id'};
528 These correspond to mysql_errno(), mysql_error(),
530 mysql_get_host_info(), mysql_info(), mysql_insert_id(),
531 mysql_get_proto_info(), mysql_get_server_info(), mysql_stat() and
532 mysql_thread_id(), respectively.
533 mysql_clientinfo
535 List information of the MySQL client library that DBD::mysql was
536 built against:
537 print "$dbh->{mysql_clientinfo}\n";
539 5.2.0-MariaDB
541 mysql_clientversion
543 print "$dbh->{mysql_clientversion}\n";
544 50200
546 mysql_serverversion
548 print "$dbh->{mysql_serverversion}\n";
549 50200
551 mysql_dbd_stats
553 $info_hashref = $dhb->{mysql_dbd_stats};
554 DBD::mysql keeps track of some statistics in the mysql_dbd_stats
556 attribute. The following stats are being maintained:
557 auto_reconnects_ok
559 The number of times that DBD::mysql successfully reconnected
560 to the mysql server.
561 auto_reconnects_failed
563 The number of times that DBD::mysql tried to reconnect to
564 mysql but failed.
565 The DBD::mysql driver also supports the following attributes of
567 database handles (read/write):
568 mysql_auto_reconnect
570 This attribute determines whether DBD::mysql will automatically
571 reconnect to mysql if the connection be lost. This feature defaults
572 to off; however, if either the GATEWAY_INTERFACE or MOD_PERL
573 environment variable is set, DBD::mysql will turn
574 mysql_auto_reconnect on. Setting mysql_auto_reconnect to on is not
575 advised if 'lock tables' is used because if DBD::mysql reconnect to
576 mysql all table locks will be lost. This attribute is ignored when
577 AutoCommit is turned off, and when AutoCommit is turned off,
578 DBD::mysql will not automatically reconnect to the server.
579 It is also possible to set the default value of the
581 "mysql_auto_reconnect" attribute for the $dbh by passing it in the
582 "\%attr" hash for "DBI-"connect>.
583 $dbh->{mysql_auto_reconnect} = 1;
585 or
587 my $dbh = DBI->connect($dsn, $user, $password, {
589 mysql_auto_reconnect => 1,
590 });
591 Note that if you are using a module or framework that performs
593 reconnections for you (for example DBIx::Connector in fixup mode),
594 this value must be set to 0.
595 mysql_use_result
597 This attribute forces the driver to use mysql_use_result rather
598 than mysql_store_result. The former is faster and less memory
599 consuming, but tends to block other processes. mysql_store_result
600 is the default due to that fact storing the result is expected
601 behavior with most applications.
602 It is possible to set the default value of the "mysql_use_result"
604 attribute for the $dbh via the DSN:
605 $dbh = DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
607 You can also set it after creation of the database handle:
609 $dbh->{mysql_use_result} = 0; # disable
611 $dbh->{mysql_use_result} = 1; # enable
612 You can also set or unset the "mysql_use_result" setting on your
614 statement handle, when creating the statement handle or after it
615 has been created. See "STATEMENT HANDLES".
616 mysql_enable_utf8
618 This attribute determines whether DBD::mysql should assume strings
619 stored in the database are utf8. This feature defaults to off.
620 When set, a data retrieved from a textual column type (char,
622 varchar, etc) will have the UTF-8 flag turned on if necessary.
623 This enables character semantics on that string. You will also
624 need to ensure that your database / table / column is configured to
625 use UTF8. See for more information the chapter on character set
626 support in the MySQL manual:
627 <http://dev.mysql.com/doc/refman/5.7/en/charset.html>
628 Additionally, turning on this flag tells MySQL that incoming data
630 should be treated as UTF-8. This will only take effect if used as
631 part of the call to connect(). If you turn the flag on after
632 connecting, you will need to issue the command "SET NAMES utf8" to
633 get the same effect.
634 mysql_enable_utf8mb4
636 This is similar to mysql_enable_utf8, but is capable of handling
637 4-byte UTF-8 characters.
638 mysql_bind_type_guessing
640 This attribute causes the driver (emulated prepare statements) to
641 attempt to guess if a value being bound is a numeric value, and if
642 so, doesn't quote the value. This was created by Dragonchild and
643 is one way to deal with the performance issue of using quotes in a
644 statement that is inserting or updating a large numeric value. This
645 was previously called "unsafe_bind_type_guessing" because it is
646 experimental. I have successfully run the full test suite with this
647 option turned on, the name can now be simply
648 "mysql_bind_type_guessing".
649 CAVEAT: Even though you can insert an integer value into a
651 character column, if this column is indexed, if you query that
652 column with the integer value not being quoted, it will not use the
653 index:
654 MariaDB [test]> explain select * from test where value0 = '3' \G
656 *************************** 1. row ***************************
657 id: 1
658 select_type: SIMPLE
659 table: test
660 type: ref
661 possible_keys: value0
662 key: value0
663 key_len: 13
664 ref: const
665 rows: 1
666 Extra: Using index condition
667 1 row in set (0.00 sec)
668 MariaDB [test]> explain select * from test where value0 = 3
670 -> \G
671 *************************** 1. row ***************************
672 id: 1
673 select_type: SIMPLE
674 table: test
675 type: ALL
676 possible_keys: value0
677 key: NULL
678 key_len: NULL
679 ref: NULL
680 rows: 6
681 Extra: Using where
682 1 row in set (0.00 sec)
683 See bug: https://rt.cpan.org/Ticket/Display.html?id=43822
685 "mysql_bind_type_guessing" can be turned on via
687 - through DSN
689 my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
691 { mysql_bind_type_guessing => 1})
692 - OR after handle creation
694 $dbh->{mysql_bind_type_guessing} = 1;
696 mysql_bind_comment_placeholders
698 This attribute causes the driver (emulated prepare statements) will
699 cause any placeholders in comments to be bound. This is not correct
700 prepared statement behavior, but some developers have come to
701 depend on this behavior, so I have made it available in 4.015
702 mysql_no_autocommit_cmd
704 This attribute causes the driver to not issue 'set autocommit'
705 either through explicit or using mysql_autocommit(). This is
706 particularly useful in the case of using MySQL Proxy.
707 See the bug report:
709 https://rt.cpan.org/Public/Bug/Display.html?id=46308
711 "mysql_no_autocommit_cmd" can be turned on when creating the
713 database handle:
714 my $dbh = DBI->connect('DBI:mysql:test', 'username', 'pass',
716 { mysql_no_autocommit_cmd => 1});
717 or using an existing database handle:
719 $dbh->{mysql_no_autocommit_cmd} = 1;
721 ping
723 This can be used to send a ping to the server.
724 $rc = $dbh->ping();
726
728 The statement handles of DBD::mysql support a number of attributes. You
729 access these by using, for example,
730 my $numFields = $sth->{NUM_OF_FIELDS};
732 Note, that most attributes are valid only after a successful execute.
734 An "undef" value will returned otherwise. The most important exception
735 is the "mysql_use_result" attribute, which forces the driver to use
736 mysql_use_result rather than mysql_store_result. The former is faster
737 and less memory consuming, but tends to block other processes. (That's
738 why mysql_store_result is the default.)
739 To set the "mysql_use_result" attribute, use either of the following:
741 my $sth = $dbh->prepare("QUERY", { mysql_use_result => 1});
743 or
745 my $sth = $dbh->prepare($sql);
747 $sth->{mysql_use_result} = 1;
748 Column dependent attributes, for example NAME, the column names, are
750 returned as a reference to an array. The array indices are
751 corresponding to the indices of the arrays returned by fetchrow and
752 similar methods. For example the following code will print a header of
753 table names together with all rows:
754 my $sth = $dbh->prepare("SELECT * FROM $table") ||
756 die "Error:" . $dbh->errstr . "\n";
757 $sth->execute || die "Error:" . $sth->errstr . "\n";
759 my $names = $sth->{NAME};
761 my $numFields = $sth->{'NUM_OF_FIELDS'} - 1;
762 for my $i ( 0..$numFields ) {
763 printf("%s%s", $i ? "," : "", $$names[$i]);
764 }
765 print "\n";
766 while (my $ref = $sth->fetchrow_arrayref) {
767 for my $i ( 0..$numFields ) {
768 printf("%s%s", $i ? "," : "", $$ref[$i]);
769 }
770 print "\n";
771 }
772 For portable applications you should restrict yourself to attributes
774 with capitalized or mixed case names. Lower case attribute names are
775 private to DBD::mysql. The attribute list includes:
776 ChopBlanks
778 this attribute determines whether a fetchrow will chop preceding
779 and trailing blanks off the column values. Chopping blanks does not
780 have impact on the max_length attribute.
781 mysql_insertid
783 If the statement you executed performs an INSERT, and there is an
784 AUTO_INCREMENT column in the table you inserted in, this attribute
785 holds the value stored into the AUTO_INCREMENT column, if that
786 value is automatically generated, by storing NULL or 0 or was
787 specified as an explicit value.
788 Typically, you'd access the value via $sth->{mysql_insertid}. The
790 value can also be accessed via $dbh->{mysql_insertid} but this can
791 easily produce incorrect results in case one database handle is
792 shared.
793 mysql_is_blob
795 Reference to an array of boolean values; TRUE indicates, that the
796 respective column is a blob. This attribute is valid for MySQL
797 only.
798 mysql_is_key
800 Reference to an array of boolean values; TRUE indicates, that the
801 respective column is a key. This is valid for MySQL only.
802 mysql_is_num
804 Reference to an array of boolean values; TRUE indicates, that the
805 respective column contains numeric values.
806 mysql_is_pri_key
808 Reference to an array of boolean values; TRUE indicates, that the
809 respective column is a primary key.
810 mysql_is_auto_increment
812 Reference to an array of boolean values; TRUE indicates that the
813 respective column is an AUTO_INCREMENT column. This is only valid
814 for MySQL.
815 mysql_length
817 mysql_max_length
818 A reference to an array of maximum column sizes. The max_length is
819 the maximum physically present in the result table, length gives
820 the theoretically possible maximum. max_length is valid for MySQL
821 only.
822 NAME
824 A reference to an array of column names.
825 NULLABLE
827 A reference to an array of boolean values; TRUE indicates that this
828 column may contain NULL's.
829 NUM_OF_FIELDS
831 Number of fields returned by a SELECT or LISTFIELDS statement. You
832 may use this for checking whether a statement returned a result: A
833 zero value indicates a non-SELECT statement like INSERT, DELETE or
834 UPDATE.
835 mysql_table
837 A reference to an array of table names, useful in a JOIN result.
838 TYPE
840 A reference to an array of column types. The engine's native column
841 types are mapped to portable types like DBI::SQL_INTEGER() or
842 DBI::SQL_VARCHAR(), as good as possible. Not all native types have
843 a meaningful equivalent, for example
844 DBD::mysql::FIELD_TYPE_INTERVAL is mapped to DBI::SQL_VARCHAR().
845 If you need the native column types, use mysql_type. See below.
846 mysql_type
848 A reference to an array of MySQL's native column types, for example
849 DBD::mysql::FIELD_TYPE_SHORT() or DBD::mysql::FIELD_TYPE_STRING().
850 Use the TYPE attribute, if you want portable types like
851 DBI::SQL_SMALLINT() or DBI::SQL_VARCHAR().
852 mysql_type_name
854 Similar to mysql, but type names and not numbers are returned.
855 Whenever possible, the ANSI SQL name is preferred.
856 mysql_warning_count
858 The number of warnings generated during execution of the SQL
859 statement. This attribute is available on both statement handles
860 and database handles.
861
863 The transaction support works as follows:
864 · By default AutoCommit mode is on, following the DBI specifications.
866 · If you execute
868 $dbh->{AutoCommit} = 0;
870 or
872 $dbh->{AutoCommit} = 1;
874 then the driver will set the MySQL server variable autocommit to 0
876 or 1, respectively. Switching from 0 to 1 will also issue a COMMIT,
877 following the DBI specifications.
878 · The methods
880 $dbh->rollback();
882 $dbh->commit();
883 will issue the commands ROLLBACK and COMMIT, respectively. A
885 ROLLBACK will also be issued if AutoCommit mode is off and the
886 database handles DESTROY method is called. Again, this is following
887 the DBI specifications.
888 Given the above, you should note the following:
890 · You should never change the server variable autocommit manually,
892 unless you are ignoring DBI's transaction support.
893 · Switching AutoCommit mode from on to off or vice versa may fail.
895 You should always check for errors when changing AutoCommit mode.
896 The suggested way of doing so is using the DBI flag RaiseError. If
897 you don't like RaiseError, you have to use code like the following:
898 $dbh->{AutoCommit} = 0;
900 if ($dbh->{AutoCommit}) {
901 # An error occurred!
902 }
903 · If you detect an error while changing the AutoCommit mode, you
905 should no longer use the database handle. In other words, you
906 should disconnect and reconnect again, because the transaction mode
907 is unpredictable. Alternatively you may verify the transaction mode
908 by checking the value of the server variable autocommit. However,
909 such behaviour isn't portable.
910 · DBD::mysql has a "reconnect" feature that handles the so-called
912 MySQL "morning bug": If the server has disconnected, most probably
913 due to a timeout, then by default the driver will reconnect and
914 attempt to execute the same SQL statement again. However, this
915 behaviour is disabled when AutoCommit is off: Otherwise the
916 transaction state would be completely unpredictable after a
917 reconnect.
918 · The "reconnect" feature of DBD::mysql can be toggled by using the
920 mysql_auto_reconnect attribute. This behaviour should be turned off
921 in code that uses LOCK TABLE because if the database server time
922 out and DBD::mysql reconnect, table locks will be lost without any
923 indication of such loss.
924
926 DBD::mysql supports multiple result sets, thanks to Guy Harrison!
927 The basic usage of multiple result sets is
929 do
931 {
932 while (@row = $sth->fetchrow_array())
933 {
934 do stuff;
935 }
936 } while ($sth->more_results)
937 An example would be:
939 $dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
941 $dbh->do("create procedure someproc() deterministic
943 begin
944 declare a,b,c,d int;
945 set a=1;
946 set b=2;
947 set c=3;
948 set d=4;
949 select a, b, c, d;
950 select d, c, b, a;
951 select b, a, c, d;
952 select c, b, d, a;
953 end") or print $DBI::errstr;
954 $sth=$dbh->prepare('call someproc()') ||
956 die $DBI::err.": ".$DBI::errstr;
957 $sth->execute || die DBI::err.": ".$DBI::errstr; $rowset=0;
959 do {
960 print "\nRowset ".++$i."\n---------------------------------------\n\n";
961 foreach $colno (0..$sth->{NUM_OF_FIELDS}-1) {
962 print $sth->{NAME}->[$colno]."\t";
963 }
964 print "\n";
965 while (@row= $sth->fetchrow_array()) {
966 foreach $field (0..$#row) {
967 print $row[$field]."\t";
968 }
969 print "\n";
970 }
971 } until (!$sth->more_results)
972 Issues with multiple result sets
974 Please be aware there could be issues if your result sets are "jagged",
975 meaning the number of columns of your results vary. Varying numbers of
976 columns could result in your script crashing.
977
979 The multithreading capabilities of DBD::mysql depend completely on the
980 underlying C libraries. The modules are working with handle data only,
981 no global variables are accessed or (to the best of my knowledge)
982 thread unsafe functions are called. Thus DBD::mysql is believed to be
983 completely thread safe, if the C libraries are thread safe and you
984 don't share handles among threads.
985 The obvious question is: Are the C libraries thread safe? In the case
987 of MySQL the answer is "mostly" and, in theory, you should be able to
988 get a "yes", if the C library is compiled for being thread safe (By
989 default it isn't.) by passing the option -with-thread-safe-client to
990 configure. See the section on How to make a threadsafe client in the
991 manual.
992
994 You can make a single asynchronous query per MySQL connection; this
995 allows you to submit a long-running query to the server and have an
996 event loop inform you when it's ready. An asynchronous query is
997 started by either setting the 'async' attribute to a true value in the
998 "do" in DBI method, or in the "prepare" in DBI method. Statements
999 created with 'async' set to true in prepare always run their queries
1000 asynchronously when "execute" in DBI is called. The driver also offers
1001 three additional methods: "mysql_async_result", "mysql_async_ready",
1002 and "mysql_fd". "mysql_async_result" returns what do or execute would
1003 have; that is, the number of rows affected. "mysql_async_ready"
1004 returns true if "mysql_async_result" will not block, and zero
1005 otherwise. They both return "undef" if that handle is not currently
1006 running an asynchronous query. "mysql_fd" returns the file descriptor
1007 number for the MySQL connection; you can use this in an event loop.
1008 Here's an example of how to use the asynchronous query interface:
1010 use feature 'say';
1012 $dbh->do('SELECT SLEEP(10)', { async => 1 });
1013 until($dbh->mysql_async_ready) {
1014 say 'not ready yet!';
1015 sleep 1;
1016 }
1017 my $rows = $dbh->mysql_async_result;
1018
1020 See DBD::mysql::INSTALL.
1021
1023 Originally, there was a non-DBI driver, Mysql, which was much like PHP
1024 drivers such as mysql and mysqli. The Mysql module was originally
1025 written by Andreas König <koenig@kulturbox.de> who still, to this day,
1026 contributes patches to DBD::mysql. An emulated version of Mysql was
1027 provided to DBD::mysql from Jochen Wiedmann, but eventually deprecated
1028 as it was another bundle of code to maintain.
1029 The first incarnation of DBD::mysql was developed by Alligator
1031 Descartes, who was also aided and abetted by Gary Shea, Andreas König
1032 and Tim Bunce.
1033 The current incarnation of DBD::mysql was written by Jochen Wiedmann,
1035 then numerous changes and bug-fixes were added by Rudy Lippan. Next,
1036 prepared statement support was added by Patrick Galbraith and Alexy
1037 Stroganov (who also solely added embedded server support).
1038 For the past nine years DBD::mysql has been maintained by Patrick
1040 Galbraith (patg@patg.net), and recently with the great help of Michiel
1041 Beijen (michiel.beijen@gmail.com), along with the entire community of
1042 Perl developers who keep sending patches to help continue improving
1043 DBD::mysql
1044
1046 Anyone who desires to contribute to this project is encouraged to do
1047 so. Currently, the source code for this project can be found at
1048 Github:
1049 <https://github.com/perl5-dbi/DBD-mysql/>
1051 Either fork this repository and produce a branch with your changeset
1053 that the maintainer can merge to his tree, or create a diff with git.
1054 The maintainer is more than glad to take contributions from the
1055 community as many features and fixes from DBD::mysql have come from the
1056 community.
1057
1059 This module is
1060 · Large Portions Copyright (c) 2004-2013 Patrick Galbraith
1062 · Large Portions Copyright (c) 2004-2006 Alexey Stroganov
1064 · Large Portions Copyright (c) 2003-2005 Rudolf Lippan
1066 · Large Portions Copyright (c) 1997-2003 Jochen Wiedmann, with code
1068 portions
1069 · Copyright (c)1994-1997 their original authors
1071
1073 This module is released under the same license as Perl itself. See
1074 <http://www.perl.com/perl/misc/Artistic.html> for details.
1075
1077 This module is maintained and supported on a mailing list, dbi-users.
1078 To subscribe to this list, send an email to
1080 dbi-users-subscribe@perl.org
1082 Mailing list archives are at
1084 <http://groups.google.com/group/perl.dbi.users?hl=en&lr=>
1086
1088 Additional information on the DBI project can be found on the World
1089 Wide Web at the following URL:
1090 <http://dbi.perl.org>
1092 where documentation, pointers to the mailing lists and mailing list
1094 archives and pointers to the most current versions of the modules can
1095 be used.
1096 Information on the DBI interface itself can be gained by typing:
1098 perldoc DBI
1100 Information on DBD::mysql specifically can be gained by typing:
1102 perldoc DBD::mysql
1104 (this will display the document you're currently reading)
1106
1108 Please report bugs, including all the information needed such as
1109 DBD::mysql version, MySQL version, OS type/version, etc to this link:
1110 <https://rt.cpan.org/Dist/Display.html?Name=DBD-mysql>
1112 Note: until recently, MySQL/Sun/Oracle responded to bugs and assisted
1114 in fixing bugs which many thanks should be given for their help! This
1115 driver is outside the realm of the numerous components they support,
1116 and the maintainer and community solely support DBD::mysql
1117perl v5.28.0 2018-02-08 DBD::mysql(3)