MySQL(PDO)

简介

PDO_MYSQL是一个驱动程序，该驱动程序实现PHP数据对象（PDO）接口以允许从PHP访问MySQL数据库。

从PHP 5.2.1开始，默认情况下PDO_MYSQL使用模拟的prepares。以前，PDO_MYSQL默认为MySQL 4.1及更高版本中的本机准备语句支持，并针对较旧版本的mysql客户端库进行仿真。

当运行7.1.16之前的PHP版本或7.2.4之前的PHP 7.2时，请将MySQL 8 Server的默认密码插件设置为mysql_native_password，否则您将看到类似于错误的消息，即使caching_sha2_password也不是客户端要求的身份验证服务器[caching_sha2_password]未使用。

这是因为MySQL 8默认为caching_sha2_password，这是旧版PHP（mysqlnd）版本无法识别的插件。而是通过在my.cnf中设置default_authentication_plugin = mysql_native_password进行更改。该caching_sha2_password插件将在未来的PHP版本中支持。同时，mysql_xdevapi扩展确实支持它。

警告
注意：某些MySQL表类型（存储引擎）不支持事务。当使用不支持事务的表类型编写事务数据库代码时，MySQL将假装事务已成功启动。此外，发出的任何DDL查询都将隐式提交任何未决的事务。

常见的Unix发行版包括可以安装的PHP二进制版本。尽管这些二进制版本通常是通过对MySQL扩展的支持而构建的，但是扩展库本身可能需要使用其他软件包来安装。检查软件包管理器，而不是您选择的发行版附带的软件包管理器。

例如，在Ubuntu上，php5-mysql软件包安装了ext / mysql，ext / mysqli和PDO_MYSQL PHP扩展。在CentOS上，php-mysql软件包还安装了这三个PHP扩展。

或者，您可以自己编译此扩展。从源代码构建PHP允许您指定要使用的MySQL扩展，以及为每个扩展选择客户端库。

编译时，使用--with-pdo-mysql[= DIR]安装PDO MySQL扩展，其中可选的[= DIR]是MySQL基础库。从PHP 5.4开始，mysqlnd是默认库。有关选择库的详细信息，请参见选择MySQL库。

（可选）--with-mysql-sock[= DIR]设置为所有MySQL扩展（包括PDO_MYSQL）的MySQL unix套接字指针的位置。如果未指定，则搜索默认位置。

（可选）--with-zlib-dir[= DIR]用于设置libz安装前缀的路径。

$ ./configure --with-pdo-mysql --with-mysql-sock = / var / mysql / mysql.sock

使用适当的PDO_MySQL常数启用SSL支持，这等效于调用»MySQL C API函数mysql_ssl_set（）。另外，不能使用PDO :: setAttribute启用SSL，因为该连接已经存在。另请参见有关»通过SSL连接到MySQL的MySQL文档。

**更新日志**
版本	说明
5.4.0	当编译PDO_MYSQL时，mysqlnd成为默认的MySQL库。以前，libmysqlclient是默认的MySQL库。
5.4.0	MySQL客户端库4.1及更低版本不再受支持。
5.3.9	通过mysqlnd和OpenSSL添加了SSL支持。
5.3.7	为libmysqlclient和OpenSSL添加了SSL支持。

预定义常量

下列常量由此驱动定义，且仅在扩展编译入 PHP或在运行时动态载入时可用。另外，使用此驱动时，仅会使用这些驱动特定的常量。使用其他驱动的驱动特定的常量可能会导致不可预见的情况。如果代码可运行于多个驱动，PDO::getAttribute()可被用于获取PDO_ATTR_DRIVER_NAME属性以检查驱动。

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY（整数）: 如果TRUE在PDOStatement上将此属性设置为，则MySQL驱动程序将使用MySQL API的缓冲版本。如果要编写可移植的代码，则应改用PDOStatement :: fetchAll（）。
强制查询在mysql中缓冲

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY(integer)

If this attribute is set to TRUE on a PDOStatement, the MySQL driver will use the buffered versions of the MySQL API. If you're writing portable code, you should use PDOStatement::fetchAll()instead.

Forcing queries to be buffered in mysql

<?php
if ($db->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql') {
    $stmt = $db->prepare('select * from foo',
        array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
} else {
    die("my application only works with mysql; I should use \$stmt->fetchAll() instead");
}
?>

PDO::MYSQL_ATTR_LOCAL_INFILE(integer)

EnableLOAD LOCAL INFILE.

Note, this constant can only be used in the$driver_optionsarray when constructing a new database handle.

PDO::MYSQL_ATTR_INIT_COMMAND(integer)

Command to execute when connecting to the MySQL server. Will automatically be re-executed when reconnecting.

Note, this constant can only be used in the$driver_optionsarray when constructing a new database handle.

PDO::MYSQL_ATTR_READ_DEFAULT_FILE(integer)

Read options from the named option file instead of frommy.cnf. This option is not available if mysqlnd is used, because mysqlnd does not read the mysql configuration files.

PDO::MYSQL_ATTR_READ_DEFAULT_GROUP(integer)

Read options from the named group frommy.cnfor the file specified with MYSQL_READ_DEFAULT_FILE. This option is not available if mysqlnd is used, because mysqlnd does not read the mysql configuration files.

PDO::MYSQL_ATTR_MAX_BUFFER_SIZE(integer)

Maximum buffer size. Defaults to 1 MiB. This constant is not supported when compiled against mysqlnd.

PDO::MYSQL_ATTR_DIRECT_QUERY(integer)

Perform direct queries, don't use prepared statements.

PDO::MYSQL_ATTR_FOUND_ROWS(integer)

Return the number of found(matched)rows, not the number of changed rows.

PDO::MYSQL_ATTR_IGNORE_SPACE(integer)

Permit spaces after function names. Makes all functions names reserved words.

PDO::MYSQL_ATTR_COMPRESS(integer)

Enable network communication compression. This is also supported when compiled against mysqlnd as of PHP 5.3.11.

PDO::MYSQL_ATTR_SSL_CA(integer)

The file path to the SSL certificate authority.

自以下版本起 PHP 5.3.7.

PDO::MYSQL_ATTR_SSL_CAPATH(integer)

The file path to the directory that contains the trusted SSL CA certificates, which are stored in PEM format.

自以下版本起 PHP 5.3.7.

PDO::MYSQL_ATTR_SSL_CERT(integer)

The file path to the SSL certificate.

自以下版本起 PHP 5.3.7.

PDO::MYSQL_ATTR_SSL_CIPHER(integer)

A list of one or more permissible ciphers to use for SSL encryption, in a format understood by OpenSSL. For example:DHE-RSA-AES256-SHA:AES128-SHA

自以下版本起 PHP 5.3.7.

PDO::MYSQL_ATTR_SSL_KEY(integer)

The file path to the SSL key.

自以下版本起 PHP 5.3.7.

PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT(integer)

Provides a way to disable verification of the server SSL certificate.

自以下版本起 PHP 7.0.18 and PHP 7.1.4.

PDO::MYSQL_ATTR_MULTI_STATEMENTS(integer)

Disables multi query execution in both PDO::prepare()and PDO::query()when set to FALSE.

Note, this constant can only be used in the$driver_optionsarray when constructing a new database handle.

自以下版本起 PHP 5.5.21 and PHP 5.6.5.

运行时配置

这些函数的行为受php.ini中的设置影响。

**PDO_MYSQL Configuration Options**
名字	默认	可修改范围
pdo_mysql.default_socket	"/tmp/mysql.sock"	PHP_INI_SYSTEM
pdo_mysql.debug	NULL	PHP_INI_SYSTEM

有关 PHP_INI_*样式的更多详情与定义，见配置可被设定范围。

这是配置指令的简短说明。

pdo_mysql.default_socketstring: Sets a Unix domain socket. This value can either be set at compile time if a domain socket is found at configure. This ini setting is Unix only.
pdo_mysql.debugboolean: Enables debugging for PDO_MYSQL. This setting is only available when PDO_MYSQL is compiled against mysqlnd and in PDO debug mode.

PDO_MYSQL DSN— Connecting to MySQL databases

There is an important undocumented attribute which disables certificate CN verification available after
5.6.22 (not sure), 7.0.18 (verified) and 7.1.15 (not sure)
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT
possible values: true, false
default value: true 
Related PHP bugs:
https://bugs.php.net/bug.php?id=71845
https://bugs.php.net/bug.php?id=71003
and github PR: https://github.com/php/php-src/pull/1913

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. ...
After spending hours trying to track down why we were getting this error on a new server, after the same code ran fine on other servers, we found the problem to be an old MySQL _client_ library running on our web server, and a latest-version MySQL _server_ running on the database server's box.
Upgraded the MySQL client on the web server to the current revision and the problem went away.

Today's PHP snapshot now has SSL support for PDO. Follow the directions here ( http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html ) to set up MySQL and then use the following connection options:
<?php
$pdo = new PDO(
  'mysql:host=hostname;dbname=ssldb',
  'username',
  'password',
  array(
    PDO::MYSQL_ATTR_SSL_KEY  =>'/path/to/client-key.pem',
    PDO::MYSQL_ATTR_SSL_CERT=>'/path/to/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_CA  =>'/path/to/ca-cert.pem'
  )
);
?>

I have been getting the error below when performing multiple queries within a single page.
Setting the attribute below did not seem to work for me.
So building on previous example i am initilizing my stmt variable on every query and a fetch all into an array. Seems to be working for me.
Error:
PDO Error 1.1: Array ( [0] => xxx[1] => yyy[2] => Lost connection to MySQL server during query )
Fix:
(PDO::setAttribute("PDO::MYSQL_ATTR_USE_BUFFERED_QUERY", true);)
<?
try {
    $dbh = new PDO('mysql:host=xxx;port=xxx;dbname=xxx', 'xxx', 'xxx', array( PDO::ATTR_PERSISTENT => false));
 $stmt = $dbh->prepare("CALL getname()");
    // call the stored procedure
    $stmt->execute();
   // fetch all rows into an array.
    $rows = $stmt->fetchAll();
    foreach ($rows as $rs) 
  {
     $id = $rs['id'];
     }
//initilise the statement
unset($stmt);
$stmt = $dbh->prepare("call secondprocedure(?);");
$stmt->bindValue(1, $id);
if ( ! $stmt->execute() )
{
  echo "PDO Error 1.1:\n";
  print_r($stmt->errorInfo());
  exit;
}
unset($stmt);
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
  }
?>

If you have the error 'could not find driver' and won't able to install driver for newer linux(Fedora, Redhat, CentOS) with latest PHP7/MariaDB10/Apache2.4 get this package installed and get every things smooth.
First download latest version of epel and remi for your distribution. This links is for Fedora 26:
wget https://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
wget https://rpms.remirepo.net/fedora/remi-release-26.rpm
Than install them
rpm -Uvh remi-release-26.rpm
rpm -Uvh epel-release-6-8.noarch.rpm
Know you can use remi repository to gest php-pdo and php-mysql.
yum --enablerepo=remi install php-pdo
yum --enablerepo=remi install php-mysql
Restart the Apache
systemctl stop httpd
systemctl start httpd
Good to go!

To use "PDO::MYSQL_ATTR_USE_BUFFERED_QUERY" you should call
PDO::setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
It will not work when passed into PDO::prepare()

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. ...
This one can be a royal pain to deal with. Never stack statements to be executed in one go. Nobody ever mentions this possibility in all the posts I've seen dealing with this error.
This example is a Zend Framework example but the theory is the same.
As in:
<?php
$sql = <<<____SQL
   CREATE TABLE IF NOT EXISTS `ticket_hist` (
    `tid` int(11) NOT NULL,
    `trqform` varchar(40) NOT NULL,
    `trsform` varchar(40) NOT NULL,
    `tgen` datetime NOT NULL,
    `tterm` datetime,
    `tstatus` tinyint(1) NOT NULL
   ) ENGINE=ARCHIVE COMMENT='ticket archive';
   CREATE TABLE IF NOT EXISTS `request_hist` (
    `rqid` int(11) NOT NULL,
    `rqtid` int(11) NOT NULL,
    `rqsid` int(11) NOT NULL,
    `rqdate` datetime NOT NULL,
    `rqcode` tinyint(1) NOT NULL,
    `rssid` int(11) NOT NULL,
    `rsdate` datetime,
    `rscode` tinyint(1)
   ) ENGINE=ARCHIVE COMMENT='request archive';
   CREATE TABLE IF NOT EXISTS `relay_hist` (
    `rqid` int(5) NOT NULL,
    `sdesc` varchar(40) NOT NULL,
    `rqemail` varchar(40) NOT NULL,
    `sid` int(11) NOT NULL,
    `rlsid` int(11) NOT NULL,
    `dcode` varchar(5) NOT NULL
   ) ENGINE=ARCHIVE COMMENT='relay archive';
____SQL;
$result = $this->db->getConnection() >exec($sql);
?>
This will run fine but PDO will balk with the 'unbuffered' error if you follow this with another query.
Instead do:
<?php
$sql = <<<____SQL
   CREATE TABLE IF NOT EXISTS `ticket_hist` (
    `tid` int(11) NOT NULL,
    `trqform` varchar(40) NOT NULL,
    `trsform` varchar(40) NOT NULL,
    `tgen` datetime NOT NULL,
    `tterm` datetime,
    `tstatus` tinyint(1) NOT NULL
   ) ENGINE=ARCHIVE COMMENT='ticket archive';
____SQL;
$result = $this->db->getConnection() >exec($sql);
$sql = <<<____SQL
   CREATE TABLE IF NOT EXISTS `request_hist` (
    `rqid` int(11) NOT NULL,
    `rqtid` int(11) NOT NULL,
    `rqsid` int(11) NOT NULL,
    `rqdate` datetime NOT NULL,
    `rqcode` tinyint(1) NOT NULL,
    `rssid` int(11) NOT NULL,
    `rsdate` datetime,
    `rscode` tinyint(1)
   ) ENGINE=ARCHIVE COMMENT='request archive';
____SQL;
$result = $this->db->getConnection() >exec($sql);
$sql = <<<____SQL
   CREATE TABLE IF NOT EXISTS `relay_hist` (
    `rqid` int(5) NOT NULL,
    `sdesc` varchar(40) NOT NULL,
    `rqemail` varchar(40) NOT NULL,
    `sid` int(11) NOT NULL,
    `rlsid` int(11) NOT NULL,
    `dcode` varchar(5) NOT NULL
   ) ENGINE=ARCHIVE COMMENT='relay archive';
____SQL;
$result = $this->db->getConnection() >exec($sql);
?>
Chopping it into individual queries fixes the problem.

This page suggests that the constant PDO::MYSQL_ATTR_FOUND_ROWS was always available (no note "exists as of X.X"), but I found the constant missing on an installation with PHP 5.2. After switching the PHP version to 5.3.27 on the webspace the constant was available.

MySQL(PDO)

简介

预定义常量

运行时配置

Table of Contents