Workerman/MySQL

Note
This library is no longer maintained. It is recommended to use illuminate/database or topthink/think-orm.

Description

Programs that reside in memory often encounter the error mysql gone away when using MySQL. This occurs because the connection between the program and MySQL has not communicated for a long time, causing the MySQL server to drop the connection. This database class can solve this problem by automatically retrying once when a mysql gone away error occurs.

Required Extensions

This MySQL class depends on the pdo and pdo_mysql extensions. Missing extensions will result in the Undefined class constant 'MYSQL_ATTR_INIT_COMMAND' in .... error.

Running the command php -m from the command line will list all installed PHP CLI extensions. If pdo or pdo_mysql is not found, please install them yourself.

CentOS System

PHP 5.x

yum install php-pdo
yum install php-mysql

PHP 7.x

yum install php70w-pdo_dblib.x86_64
yum install php70w-mysqlnd.x86_64

If you cannot find the package name, please try using yum search php mysql to search.

Ubuntu/Debian System

PHP 5.x

apt-get install php5-mysql

PHP 7.x

apt-get install php7.0-mysql

If you cannot find the package name, please try using apt-cache search php mysql to search.

Unable to install using the above methods?

If the above methods do not work, please refer to Workerman Manual - Appendix - Extend Installation - Method 3 Source Code Compilation Installation.

Install Workerman/MySQL

Method 1:

You can install it via composer. Run the following command in the command line (the composer source is abroad, and the installation process may be very slow).

composer require workerman/mysql

The above command will generate a vendor directory after success, and then include the autoload.php under the vendor in your project.

require_once __DIR__ . '/vendor/autoload.php';

Method 2:

Download the source code, place the extracted directory into your project (location is arbitrary), and directly require the source file.

require_once '/your/path/of/mysql-master/src/Connection.php';

Note

It is strongly recommended to initialize the database connection in the onWorkerStart callback to avoid initializing the connection before Worker::runAll();. If the connection is initialized before Worker::runAll();, it will belong to the main process, and the child processes will inherit this connection. Sharing the same database connection between the main process and child processes will cause errors.

Example

use Workerman\Worker;
use Workerman\Connection\TcpConnection;
require_once __DIR__ . '/vendor/autoload.php';

$worker = new Worker('websocket://0.0.0.0:8484');
$worker->onWorkerStart = function($worker)
{
    // Store the db instance in a global variable (can also be stored in a static member of a class)
    global $db;
    $db = new \Workerman\MySQL\Connection('host', 'port', 'user', 'password', 'db_name');
};
$worker->onMessage = function(TcpConnection $connection, $data)
{
    // Get the db instance via the global variable
    global $db;
    // Execute SQL
    $all_tables = $db->query('show tables');
    $connection->send(json_encode($all_tables));
};
// Run worker
Worker::runAll();

Specific MySQL/Connection Usage

// Initialize db connection
$db = new \Workerman\MySQL\Connection('host', 'port', 'user', 'password', 'db_name');

// Get all data
$db->select('ID,Sex')->from('Persons')->where('sex= :sex AND ID = :id')->bindValues(array('sex'=>'M', 'id' => 1))->query();
// Equivalent to
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' AND ID = 1")->query();
// Equivalent to
$db->query("SELECT ID,Sex FROM `Persons` WHERE sex='M' AND ID = 1");

// Get one row of data
$db->select('ID,Sex')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->row();
// Equivalent to
$db->select('ID,Sex')->from('Persons')->where("sex= 'M' ")->row();
// Equivalent to
$db->row("SELECT ID,Sex FROM `Persons` WHERE sex='M'");

// Get one column of data
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->column();
// Equivalent to
$db->select('ID')->from('Persons')->where("sex= 'F' ")->column();
// Equivalent to
$db->column("SELECT `ID` FROM `Persons` WHERE sex='M'");

// Get a single value
$db->select('ID')->from('Persons')->where('sex= :sex')->bindValues(array('sex'=>'M'))->single();
// Equivalent to
$db->select('ID')->from('Persons')->where("sex= 'F' ")->single();
// Equivalent to
$db->single("SELECT ID FROM `Persons` WHERE sex='M'");

// Complex query
$db->select('*')->from('table1')->innerJoin('table2','table1.uid = table2.uid')->where('age > :age')->groupBy(array('aid'))->having('foo="foo"')->orderByASC/*orderByDESC*/(array('did'))
->limit(10)->offset(20)->bindValues(array('age' => 13));
// Equivalent to
$db->query('SELECT * FROM `table1` INNER JOIN `table2` ON `table1`.`uid` = `table2`.`uid`
WHERE age > 13 GROUP BY aid HAVING foo="foo" ORDER BY did LIMIT 10 OFFSET 20');

// Insert
$insert_id = $db->insert('Persons')->cols(array(
    'Firstname'=>'abc',
    'Lastname'=>'efg',
    'Sex'=>'M',
    'Age'=>13))->query();
Equivalent to
$insert_id = $db->query("INSERT INTO `Persons` ( `Firstname`,`Lastname`,`Sex`,`Age`)
VALUES ( 'abc', 'efg', 'M', 13)");

// Update
$row_count = $db->update('Persons')->cols(array('sex'))->where('ID=1')
->bindValue('sex', 'F')->query();
// Equivalent to
$row_count = $db->update('Persons')->cols(array('sex'=>'F'))->where('ID=1')->query();
// Equivalent to
$row_count = $db->query("UPDATE `Persons` SET `sex` = 'F' WHERE ID=1");

// Delete
$row_count = $db->delete('Persons')->where('ID=9')->query();
// Equivalent to
$row_count = $db->query("DELETE FROM `Persons` WHERE ID=9");

// Transaction
$db->beginTrans();
....
$db->commitTrans(); // or $db->rollBackTrans();