1DBD::mysql(3)         User Contributed Perl Documentation        DBD::mysql(3)
2
3
4

NAME

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

SYNOPSIS

9           use DBI;
10
11           my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
12           my $dbh = DBI->connect($dsn, $user, $password);
13
14           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

EXAMPLE

25         #!/usr/bin/perl
26
27         use strict;
28         use warnings;
29         use DBI;
30
31         # Connect to the database.
32         my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost",
33                                "joe", "joe's password",
34                                {'RaiseError' => 1});
35
36         # 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
41         # 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
45         # 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
49         # same thing, but using placeholders (recommended!)
50         $dbh->do("INSERT INTO foo VALUES (?, ?)", undef, 2, "Jochen");
51
52         # 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
60         # Disconnect from the database.
61         $dbh->disconnect();
62

DESCRIPTION

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
71       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
76       From perl you activate the interface with the statement
77
78         use DBI;
79
80       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
85         $dbh = DBI->connect("DBI:mysql:database=$db;host=$host",
86           $user, $password, {RaiseError => 1});
87
88       Once you have connected to a database, you can execute SQL statements
89       with:
90
91         my $query = sprintf("INSERT INTO foo VALUES (%d, %s)",
92                             $number, $dbh->quote("name"));
93         $dbh->do($query);
94
95       See DBI for details on the quote and do methods. An alternative
96       approach is
97
98         $dbh->do("INSERT INTO foo VALUES (?, ?)", undef,
99                  $number, $name);
100
101       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
105       If you want to retrieve results, you need to create a so-called
106       statement handle with:
107
108         $sth = $dbh->prepare("SELECT * FROM $table");
109         $sth->execute();
110
111       This statement handle can be used for multiple things. First of all you
112       can retrieve a row of data:
113
114         my $row = $sth->fetchrow_hashref();
115
116       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
120       But now for a more formal approach:
121
122   Class Methods
123       connect
124               use DBI;
125
126               $dsn = "DBI:mysql:$database";
127               $dsn = "DBI:mysql:database=$database;host=$hostname";
128               $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
129
130               $dbh = DBI->connect($dsn, $user, $password);
131
132           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
138           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
142           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
150               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
156               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
160               When connecting to a MySQL Server with IPv6, a bracketed IPv6
161               address should be used.  Example DSN:
162
163                 my $dsn = "DBI:mysql:;host=[1a12:2800:6f2:85::f20:8cf];port=3306";
164
165           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
171                 UPDATE $table SET id = 1 WHERE id = 1;
172
173               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
178           mysql_compression
179               If your DSN contains the option "mysql_compression=1", then the
180               communication between client and server will be compressed.
181
182           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
187           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
192           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
197           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
203           mysql_skip_secure_auth
204               This option is for older mysql databases that don't have secure
205               auth set.
206
207           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
216                   $dsn = "DBI:mysql:test;mysql_read_default_file=/home/joe/my.cnf";
217                   $dbh = DBI->connect($dsn, $user, $password)
218
219               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
223                   [client]
224                   host=localhost
225
226                   [perl]
227                   host=perlhost
228
229               (Note the order of the entries! The example won't work, if you
230               reverse the [client] and [perl] sections!)
231
232               If you read this config file, then you'll be typically
233               connected to localhost. However, by using
234
235                   $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
239               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
244           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
248                   mysql_socket=/dev/mysql
249
250               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
254           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
260               When enabling SSL encryption you should set also other SSL
261               options, at least mysql_ssl_ca_file or mysql_ssl_ca_path.
262
263                 mysql_ssl=1 mysql_ssl_verify_server_cert=1 mysql_ssl_ca_file=/path/to/ca_cert.pem
264
265               This means that your communication with the server will be
266               encrypted.
267
268               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
272           mysql_ssl_ca_file
273               The path to a file in PEM format that contains a list of
274               trusted SSL certificate authorities.
275
276               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
280           mysql_ssl_ca_path
281               The path to a directory that contains trusted SSL certificate
282               authority certificates in PEM format.
283
284               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
288               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
292           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
300               Verification of the host name is disabled by default.
301
302           mysql_ssl_client_key
303               The name of the SSL key file in PEM format to use for
304               establishing a secure connection.
305
306           mysql_ssl_client_cert
307               The name of the SSL certificate file in PEM format to use for
308               establishing a secure connection.
309
310           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
315                 mysql_ssl_cipher=AES128-SHA
316                 mysql_ssl_cipher=DHE-RSA-AES256-SHA:AES128-SHA
317
318           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
325               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
336           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
341           mysql_get_server_pubkey
342               Setting "mysql_get_server_pubkey" to true requests the public
343               RSA key of the server.
344
345           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
352           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
358           mysql_server_prepare
359               This option is used to enable server side prepared statements.
360
361               To use server side prepared statements, all you need to do is
362               set the variable mysql_server_prepare in the connect:
363
364                 $dbh = DBI->connect(
365                   "DBI:mysql:database=test;host=localhost;mysql_server_prepare=1",
366                   "",
367                   "",
368                   { RaiseError => 1, AutoCommit => 1 }
369                 );
370
371               or:
372
373                 $dbh = DBI->connect(
374                   "DBI:mysql:database=test;host=localhost",
375                   "",
376                   "",
377                   { RaiseError => 1, AutoCommit => 1, mysql_server_prepare => 1 }
378                 );
379
380               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
385               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
389                 export MYSQL_SERVER_PREPARE=1
390
391               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
395           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
400               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
404           mysql_embedded_options
405               The option <mysql_embedded_options> can be used to pass
406               'command-line' options to embedded server.
407
408               Example:
409
410                 use DBI;
411                 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_options=--help,--verbose";
412                 $dbh = DBI->connect($testdsn,"a","b");
413
414               This would cause the command line help to the embedded MySQL
415               server library to be printed.
416
417           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
423               Example:
424
425                 $testdsn="DBI:mysqlEmb:database=test;mysql_embedded_groups=embedded_server,common";
426
427           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
434               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
440                 my $dbh= DBI->connect($dsn, $user, $password,
441                   { AutoCommit => 0,
442                     mysql_conn_attrs => {
443                       foo => 'bar',
444                       wiz => 'bang'
445                     },
446                   });
447
448               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
453                 my $results = $dbh->selectall_hashref(
454                   'SELECT * FROM performance_schema.session_connect_attrs',
455                   'ATTR_NAME'
456                 );
457
458               This returns:
459
460                 $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
511   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
518           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
522               @dbs = DBI->data_sources("mysql");
523

DATABASE HANDLES

525       The DBD::mysql driver supports the following attributes of database
526       handles (read only):
527
528         $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
538       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
543       mysql_clientinfo
544         List information of the MySQL client library that DBD::mysql was
545         built against:
546
547           print "$dbh->{mysql_clientinfo}\n";
548
549           5.2.0-MariaDB
550
551       mysql_clientversion
552           print "$dbh->{mysql_clientversion}\n";
553
554           50200
555
556       mysql_serverversion
557           print "$dbh->{mysql_serverversion}\n";
558
559           50200
560
561       mysql_dbd_stats
562           $info_hashref = $dbh->{mysql_dbd_stats};
563
564         DBD::mysql keeps track of some statistics in the mysql_dbd_stats
565         attribute.  The following stats are being maintained:
566
567         auto_reconnects_ok
568                 The number of times that DBD::mysql successfully reconnected
569                 to the mysql server.
570
571         auto_reconnects_failed
572                 The number of times that DBD::mysql tried to reconnect to
573                 mysql but failed.
574
575       The DBD::mysql driver also supports the following attributes of
576       database handles (read/write):
577
578       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
589           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
593             $dbh->{mysql_auto_reconnect} = 1;
594
595           or
596
597             my $dbh = DBI->connect($dsn, $user, $password, {
598                mysql_auto_reconnect => 1,
599             });
600
601           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
605       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
612           It is possible to set the default value of the "mysql_use_result"
613           attribute for the $dbh via the DSN:
614
615             $dbh = DBI->connect("DBI:mysql:test;mysql_use_result=1", "root", "");
616
617           You can also set it after creation of the database handle:
618
619              $dbh->{mysql_use_result} = 0; # disable
620              $dbh->{mysql_use_result} = 1; # enable
621
622           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
626       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
630           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
638           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
644       mysql_enable_utf8mb4
645           This is similar to mysql_enable_utf8, but is capable of handling
646           4-byte UTF-8 characters.
647
648       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
659           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
664               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
678               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
693           See bug: https://rt.cpan.org/Ticket/Display.html?id=43822
694
695           "mysql_bind_type_guessing" can be turned on via
696
697            - through DSN
698
699             my $dbh= DBI->connect('DBI:mysql:test', 'username', 'pass',
700             { mysql_bind_type_guessing => 1})
701
702             - OR after handle creation
703
704             $dbh->{mysql_bind_type_guessing} = 1;
705
706       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
712       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
717           See the bug report:
718
719           https://rt.cpan.org/Public/Bug/Display.html?id=46308
720
721           "mysql_no_autocommit_cmd" can be turned on when creating the
722           database handle:
723
724             my $dbh = DBI->connect('DBI:mysql:test', 'username', 'pass',
725             { mysql_no_autocommit_cmd => 1});
726
727           or using an existing database handle:
728
729             $dbh->{mysql_no_autocommit_cmd} = 1;
730
731       ping
732           This can be used to send a ping to the server.
733
734             $rc = $dbh->ping();
735

STATEMENT HANDLES

737       The statement handles of DBD::mysql support a number of attributes. You
738       access these by using, for example,
739
740         my $numFields = $sth->{NUM_OF_FIELDS};
741
742       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
749       To set the "mysql_use_result" attribute, use either of the following:
750
751         my $sth = $dbh->prepare("QUERY", { mysql_use_result => 1});
752
753       or
754
755         my $sth = $dbh->prepare($sql);
756         $sth->{mysql_use_result} = 1;
757
758       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
764         my $sth = $dbh->prepare("SELECT * FROM $table") ||
765           die "Error:" . $dbh->errstr . "\n";
766
767         $sth->execute ||  die "Error:" . $sth->errstr . "\n";
768
769         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
782       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
786       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
791       mysql_gtids
792           Returns GTID(s) if GTID session tracking is ensabled in the server
793           via session_track_gtids.
794
795       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
802           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
807       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
812       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
816       mysql_is_num
817           Reference to an array of boolean values; TRUE indicates, that the
818           respective column contains numeric values.
819
820       mysql_is_pri_key
821           Reference to an array of boolean values; TRUE indicates, that the
822           respective column is a primary key.
823
824       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
829       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
836       NAME
837           A reference to an array of column names.
838
839       NULLABLE
840           A reference to an array of boolean values; TRUE indicates that this
841           column may contain NULL's.
842
843       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
849       mysql_table
850           A reference to an array of table names, useful in a JOIN result.
851
852       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
860       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
866       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
870       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

TRANSACTION SUPPORT

876       The transaction support works as follows:
877
878       •   By default AutoCommit mode is on, following the DBI specifications.
879
880       •   If you execute
881
882             $dbh->{AutoCommit} = 0;
883
884           or
885
886             $dbh->{AutoCommit} = 1;
887
888           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
892       •   The methods
893
894               $dbh->rollback();
895               $dbh->commit();
896
897           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
902       Given the above, you should note the following:
903
904       •   You should never change the server variable autocommit manually,
905           unless you are ignoring DBI's transaction support.
906
907       •   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
912             $dbh->{AutoCommit} = 0;
913             if ($dbh->{AutoCommit}) {
914               # An error occurred!
915             }
916
917       •   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
924       •   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
932       •   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

MULTIPLE RESULT SETS

939       DBD::mysql supports multiple result sets, thanks to Guy Harrison!
940
941       The basic usage of multiple result sets is
942
943         do
944         {
945           while (@row = $sth->fetchrow_array())
946           {
947             do stuff;
948           }
949         } while ($sth->more_results)
950
951       An example would be:
952
953         $dbh->do("drop procedure if exists someproc") or print $DBI::errstr;
954
955         $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
968         $sth=$dbh->prepare('call someproc()') ||
969         die $DBI::err.": ".$DBI::errstr;
970
971         $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
986   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

MULTITHREADING

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
999       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

ASYNCHRONOUS QUERIES

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
1019       Here's an example of how to use the asynchronous query interface:
1020
1021         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

INSTALLATION

1030       See DBD::mysql::INSTALL.
1031

AUTHORS

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
1040       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
1044       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
1049       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

CONTRIBUTIONS

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
1060       <https://github.com/perl5-dbi/DBD-mysql/>
1061
1062       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
1071       •   Large Portions Copyright (c) 2004-2013 Patrick Galbraith
1072
1073       •   Large Portions Copyright (c) 2004-2006 Alexey Stroganov
1074
1075       •   Large Portions Copyright (c) 2003-2005 Rudolf Lippan
1076
1077       •   Large Portions Copyright (c) 1997-2003 Jochen Wiedmann, with code
1078           portions
1079
1080       •   Copyright (c)1994-1997 their original authors
1081

LICENSE

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

MAILING LIST SUPPORT

1087       This module is maintained and supported on a mailing list, dbi-users.
1088
1089       To subscribe to this list, send an email to
1090
1091       dbi-users-subscribe@perl.org
1092
1093       Mailing list archives are at
1094
1095       <http://groups.google.com/group/perl.dbi.users?hl=en&lr=>
1096

ADDITIONAL DBI INFORMATION

1098       Additional information on the DBI project can be found on the World
1099       Wide Web at the following URL:
1100
1101       <http://dbi.perl.org>
1102
1103       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
1107       Information on the DBI interface itself can be gained by typing:
1108
1109           perldoc DBI
1110
1111       Information on DBD::mysql specifically can be gained by typing:
1112
1113           perldoc DBD::mysql
1114
1115       (this will display the document you're currently reading)
1116

BUG REPORTING, ENHANCEMENT/FEATURE REQUESTS

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
1121       <https://rt.cpan.org/Dist/Display.html?Name=DBD-mysql>
1122
1123       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
1127
1128
1129
1130perl v5.36.0                      2022-07-22                     DBD::mysql(3)
Impressum