mysql.pm

Astercon2 开源软交换 2.2.0

=item mysql_connect_timeout

If your DSN contains the option "mysql_connect_timeout=##", the connect
request to the server will timeout if it has not been successful after
the given number of seconds.

=item mysql_read_default_file

=item mysql_read_default_group

These options can be used to read a config file like /etc/my.cnf or
~/.my.cnf. By default MySQL's C client library doesn't use any config
files unlike the client programs (mysql, mysqladmin, ...) that do, but
outside of the C client library. Thus you need to explicitly request
reading a config file, as in

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

The option mysql_read_default_group can be used to specify the default
group in the config file: Usually this is the I<client> group, but
see the following example:

    [client]
    host=localhost

    [perl]
    host=perlhost

(Note the order of the entries! The example won't work, if you reverse
the [client] and [perl] sections!)

If you read this config file, then you'll be typically connected to
I<localhost>. However, by using

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

you'll be connected to I<perlhost>. Note that if you specify a
default group and do not specify a file, then the default config
files will all be read.  See the documentation of
the C function mysql_options() for details.

=item mysql_socket

As of MySQL 3.21.15, it is possible to choose the Unix socket that is
used for connecting to the server. This is done, for example, with

    mysql_socket=/dev/mysql

Usually there's no need for this option, unless you are using another
location for the socket than that built into the client.

=item mysql_ssl

A true value turns on the CLIENT_SSL flag when connecting to the MySQL
database:

  mysql_ssl=1

This means that your communication with the server will be encrypted.

If you turn mysql_ssl on, you might also wish to use the following
flags:

=item mysql_ssl_client_key

=item mysql_ssl_client_cert

=item mysql_ssl_ca_file

=item mysql_ssl_ca_path

=item mysql_ssl_cipher

These are used to specify the respective parameters of a call
to mysql_ssl_set, if mysql_ssl is turned on.  


=item mysql_local_infile

As of MySQL 3.23.49, the LOCAL capability for LOAD DATA may be disabled
in the MySQL client library by default. If your DSN contains the option
"mysql_local_infile=1", LOAD DATA LOCAL will be enabled.  (However,
this option is *ineffective* if the server has also been configured to
disallow LOCAL.)

=item mysql_multi_statements

As of MySQL 4.1, support for multiple statements seperated by a semicolon
(;) may be enabled by using this option. Enabling this option may cause
problems if server-side prepared statements are also enabled.

=item Prepared statement support (server side prepare)

As of 3.0002_1, server side prepare statements were on by default (if your
server was >= 4.1.3). As of 3.0009, they were off by default again due to 
issues with the prepared statement API (all other mysql connectors are
set this way until C API issues are resolved). The requirement to use
prepared statements still remains that you have a server >= 4.1.3

To use server side prepared statements, all you need to do is set the variable 
mysql_server_prepare in the connect:

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

* Note: delimiter for this param is ';'

There are many benefits to using server side prepare statements, mostly if you are 
performing many inserts because of that fact that a single statement is prepared 
to accept multiple insert values.

To make sure that the 'make test' step tests whether server prepare works, you just
need to export the env variable MYSQL_SERVER_PREPARE:

export MYSQL_SERVER_PREPARE=1


=item mysql_embedded_options

The option <mysql_embedded_options> can be used to pass 'command-line' 
options to embedded server.

Example:

$testdsn="DBI:mysqlEmb:database=test;mysql_embedded_options=--help,--verbose";


=item mysql_embedded_groups

The option <mysql_embedded_groups> can be used to specify the groups in the 
config file(I<my.cnf>) which will be used to get options for embedded server. 
If not specified [server] and [embedded] groups will be used.

Example:

$testdsn="DBI:mysqlEmb:database=test;mysql_embedded_groups=embedded_server,common";


=back

=back


=head2 Private MetaData Methods

=over

=item B<ListDBs>

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

Returns a list of all databases managed by the MySQL server
running on C<$hostname>, port C<$port>. This is a legacy
method.  Instead, you should use the portable method

    @dbs = DBI->data_sources("mysql");

=back


=head2 Server Administration

=over

=item admin

    $rc = $drh->func("createdb", $dbname, [host, user, password,], 'admin');
    $rc = $drh->func("dropdb", $dbname, [host, user, password,], 'admin');
    $rc = $drh->func("shutdown", [host, user, password,], 'admin');
    $rc = $drh->func("reload", [host, user, password,], 'admin');

      or

    $rc = $dbh->func("createdb", $dbname, 'admin');
    $rc = $dbh->func("dropdb", $dbname, 'admin');
    $rc = $dbh->func("shutdown", 'admin');
    $rc = $dbh->func("reload", 'admin');

For server administration you need a server connection. For obtaining
this connection you have two options: Either use a driver handle (drh)
and supply the appropriate arguments (host, defaults localhost, user,
defaults to '' and password, defaults to ''). A driver handle can be
obtained with

    $drh = DBI->install_driver('mysql');

Otherwise reuse the existing connection of a database handle (dbh).

There's only one function available for administrative purposes, comparable
to the m(y)sqladmin programs. The command being execute depends on the
first argument:

=over

=item createdb

Creates the database $dbname. Equivalent to "m(y)sqladmin create $dbname".

=item dropdb

Drops the database $dbname. Equivalent to "m(y)sqladmin drop $dbname".

It should be noted that database deletion is
I<not prompted for> in any way.  Nor is it undo-able from DBI.

    Once you issue the dropDB() method, the database will be gone!

These method should be used at your own risk.

=item shutdown

Silently shuts down the database engine. (Without prompting!)
Equivalent to "m(y)sqladmin shutdown".

=item reload

Reloads the servers configuration files and/or tables. This can be particularly
important if you modify access privileges or create new users.

=back

=back


=head1 DATABASE HANDLES

The DBD::mysql driver supports the following attributes of database
handles (read only):

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

These correspond to mysql_errno(), mysql_error(), mysql_get_host_info(),
mysql_info(), mysql_insert_id(), mysql_get_proto_info(),
mysql_get_server_info(), mysql_stat() and mysql_thread_id(),
respectively.


 $info_hashref = $dhb->{mysql_dbd_stats}

DBD::mysql keeps track of some statistics in the mysql_dbd_stats attribute.
The following stats are being maintained:

=over

=item auto_reconnects_ok

The number of times that DBD::mysql successfully reconnected to the mysql 
server.

=item auto_reconnects_failed

The number of times that DBD::mysql tried to reconnect to mysql but failed.

=back

The DBD::mysql driver also supports the following attribute(s) of database
handles (read/write):

 $bool_value = $dbh->{mysql_auto_reconnect};
 $dbh->{mysql_auto_reconnect} = $AutoReconnect ? 1 : 0;


=item mysql_auto_reconnect

This attribute determines whether DBD::mysql will automatically reconnect
to mysql if the connection be lost. This feature defaults to off; however,
if either the GATEWAY_INTERFACE or MOD_PERL envionment variable is set, 
DBD::mysql will turn mysql_auto_reconnect on.  Setting mysql_auto_reconnect 
to on is not advised if 'lock tables' is used because if DBD::mysql reconnect 
to mysql all table locks will be lost.  This attribute is ignored when
AutoCommit is turned off, and when AutoCommit is turned off, DBD::mysql will
not automatically reconnect to the server.

=item mysql_use_result

This attribute forces the driver to use mysql_use_result rather than
mysql_store_result. The former is faster and less memory consuming, but
tends to block other processes. (That's why mysql_store_result is the
default.)

It is possible to set default value of the C<mysql_use_result> attribute 
for $dbh using several ways:

 - through DSN 

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

 - after creation of database handle

   $dbh->{'mysql_use_result'}=0; #disable
   $dbh->{'mysql_use_result'}=1; #enable

It is possible to set/unset the C<mysql_use_result> attribute after 
creation of statement handle. See below.

=item mysql_enable_utf8

This attribute determines whether DBD::mysql should assume strings
stored in the database are utf8.  This feature defaults to off.

When set, a data retrieved from a textual column type (char, varchar,
etc) will have the UTF-8 flag turned on if necessary.  This enables
character semantics on that string.  You will also need to ensure that
your database / table / column is configured to use UTF8.  See Chapter
10 of the mysql manual for details.

Additionally, turning on this flag tells MySQL that incoming data should
be treated as UTF-8.  This will only take effect if used as part of the
call to connect().  If you turn the flag on after connecting, you will
need to issue the command C<SET NAMES utf8> to get the same effect.

This option is experimental and may change in future versions.

=head1 STATEMENT HANDLES

The statement handles of DBD::mysql support a number
of attributes. You access these by using, for example,

  my $numFields = $sth->{'NUM_OF_FIELDS'};

Note, that most attributes are valid only after a successfull I<execute>.
An C<undef> value will returned in that case. The most important exception
is the C<mysql_use_result> attribute: This forces the driver to use
mysql_use_result rather than mysql_store_result. The former is faster
and less memory consuming, but tends to block other processes. (That's why
mysql_store_result is the default.)

To set the C<mysql_use_result> attribute, use either of the following:

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

or

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

Column dependent attributes, for example I<NAME>, the column names,
are returned as a reference to an array. The array indices are
corresponding to the indices of the arrays returned by I<fetchrow>
and similar methods. For example the following code will print a
header of table names together with all rows:

  my $sth = $dbh->prepare("SELECT * FROM $table");
  if (!$sth) {
      die "Error:" . $dbh->errstr . "\n";
  }
  if (!$sth->execute) {
      die "Error:" . $sth->errstr . "\n";
  }
  my $names = $sth->{'NAME'};
  my $numFields = $sth->{'NUM_OF_FIELDS'};
  for (my $i = 0;  $i < $numFields;  $i++) {
      printf("%s%s", $i ? "," : "", $$names[$i]);
  }
  print "\n";
  while (my $ref = $sth->fetchrow_arrayref) {
      for (my $i = 0;  $i < $numFields;  $i++) {
	  printf("%s%s", $i ? "," : "", $$ref[$i]);
      }
      print "\n";
  }

For portable applications you should restrict yourself to attributes with
capitalized or mixed case names. Lower case attribute names are private
to DBD::mysql. The attribute list includes:

=over

=item ChopBlanks

this attribute determines whether a I<fetchrow> will chop preceding
and trailing blanks off the column values. Chopping blanks does not
have impact on the I<max_length> attribute.

=item mysql_insertid

MySQL has the ability to choose unique key values automatically. If this
happened, the new ID will be stored in this attribute. An alternative
way for accessing this attribute is via $dbh->{'mysql_insertid'}.
(Note we are using the $dbh in this case!)

=item mysql_is_blob

Reference to an array of boolean values; TRUE indicates, that the
respective column is a blob. This attribute is valid for MySQL only.

=item mysql_is_key

Reference to an array of boolean values; TRUE indicates, that the
respective column is a key. This is valid for MySQL only.

=item mysql_is_num

Reference to an array of boolean values; TRUE indicates, that the
respective column contains numeric values.

=item mysql_is_pri_key

Reference to an array of boolean values; TRUE indicates, that the
respective column is a primary key.

=item mysql_is_auto_increment

Reference to an array of boolean values; TRUE indicates that the
respective column is an AUTO_INCREMENT column.  This is only valid
for MySQL.

=item mysql_length

=item mysql_max_length

A reference to an array of maximum column sizes. The I<max_length> is
the maximum physically present in the result table, I<length> gives
the theoretically possible maximum. I<max_length> is valid for MySQL
only.

=item NAME

A reference to an array of column names.

=item NULLABLE

A reference to an array of boolean values; TRUE indicates that this column
may contain NULL's.

=item NUM_OF_FIELDS

Number of fields returned by a I<SELECT> or I<LISTFIELDS> statement.
You may use this for checking whether a statement returned a result:
A zero value indicates a non-SELECT statement like I<INSERT>,
I<DELETE> or I<UPDATE>.

=item mysql_table

A reference to an array of table names, useful in a I<JOIN> result.

=item TYPE

A reference to an array of column types. The engine's native column
types are mapped to portable types like DBI::SQL_INTEGER() or
DBI::SQL_VARCHAR(), as good as possible. Not all native types have
a meaningfull equivalent, for example DBD::mysql::FIELD_TYPE_INTERVAL
is mapped to DBI::SQL_VARCHAR().
If you need the native column types, use I<mysql_type>. See below.

=item mysql_type

A reference to an array of MySQL's native column types, for example
DBD::mysql::FIELD_TYPE_SHORT() or DBD::mysql::FIELD_TYPE_STRING().
Use the I<TYPE> attribute, if you want portable types like
DBI::SQL_SMALLINT() or DBI::SQL_VARCHAR().

=item mysql_type_name