ThinkPHP database operations stored procedures, data sets, distributed databases

The following tutorial column will introduce you to the stored procedures, data sets, and distributed databases of ThinkPHP database operations. I hope it will be helpful to friends in need!

Stored Procedure

5.0 supports stored procedures, if we define A database stored procedure sp_query can be called in the following way:

$result = Db::query('call sp_query(8)');

returns a two-dimensional array, or you can use parameter binding, for example:

$result = Db::query('call sp_query(?)',[8]);
// 或者命名绑定$result = Db::query('call sp_query(:id)',['id'=>8]);

Dataset

The query result of the database is also the data set. Under the default configuration, the type of the data set is a two-dimensional array. We can configure it into a data set. class, you can support more object-based operations on the data set. You need to use the data set class function. You can configure the resultset_type parameter of the database as follows:

return [    // 数据库类型
    'type' => 'mysql',    // 数据库连接DSN配置
    'dsn' => '',    // 服务器地址
    'hostname' => '127.0.0.1',    // 数据库名
    'database' => 'thinkphp',    // 数据库用户名
    'username' => 'root',    // 数据库密码
    'password' => '',    // 数据库连接端口
    'hostport' => '',    // 数据库连接参数
    'params' => [],    // 数据库编码默认采用utf8
    'charset' => 'utf8',    // 数据库表前缀
    'prefix' => 'think_',    // 数据集返回类型
    'resultset_type' => 'collection',];

The returned data set object is think\Collection, which provides and Arrays are used indiscriminately and also encapsulate some additional methods. You can directly use the array method to operate the data set object, for example:

// 获取数据集
$users = Db::name('user')->select();
// 直接操作第一个元素
$item = $users[0];
// 获取数据集记录数
$count = count($users);
// 遍历数据集
foreach($users as $user){    echo $user['name'];    echo $user['id'];
}

It should be noted that if you want to judge whether the data set is empty, you cannot directly use empty to judge, but you must use the data set object's isEmpty method judgment, for example:

$users = Db::name('user')->select();if($users->isEmpty()){    echo '数据集为空';
}

The Collection class contains the following main methods:

If the query of only individual data needs to return the data set object , you can use

Db::name('user')    ->fetchClass('\think\Collection')    ->select();

Distributed database

##ThinkPHP has built-in support for distributed databases, including master-slave The read and write separation of the traditional database, but the distributed database must be the same database type. Configuring database.deploy to 1 can use distributed database support. If a distributed database is used, the way to define database configuration information is as follows:

//分布式数据库配置定义
return [    
// 启用分布式数据库
    'deploy' => 1,    // 数据库类型
    'type' => 'mysql',    // 服务器地址
    'hostname' => '192.168.1.1,192.168.1.2',    // 数据库名
    'database' => 'demo',    // 数据库用户名
    'username' => 'root',    // 数据库密码
    'password' => '',    // 数据库连接端口
    'hostport' => '',]

The number of connected databases depends on the number of hostname definitions, so even two identical IPs need to be defined repeatedly, but other parameters If there are the same ones, there is no need to repeat the definition. For example:

'hostport'=>'3306,3306'

and

'hostport'=>'3306'

are equivalent.

'username'=>'user1',
'password'=>'pwd1',

is equivalent to

'username'=>'user1,user1',
'password'=>'pwd1,pwd1',

.

You can also set whether the reading and writing of the distributed database are separated. By default, reading and writing are not separated, that is, each server can perform read and write operations. For a master-slave database, you need to set the read and write operations. Write separation can be achieved through the following settings:

'rw_separate' => true,

In the case of read and write separation, the default first database configuration is the configuration information of the main server, which is responsible for writing data. If the master_num parameter is set, then Can support multiple master server writes. Others are responsible for reading data from the configuration information of the database, and the number is not limited. Each time you connect to the slave server and perform a read operation, the system will randomly select from the slave server.

You can also set slave_no to specify a server for read operations.

　

If the slave database connection error occurs, it will automatically switch to the master database connection.

If you call the CURD operation of the model, the system will automatically determine whether the currently executed method is a read operation or a write operation. If you are using native SQL, you need to pay attention to the system's default rules:

Write operations must use the model's execute method, and read operations must use the model's query method

, otherwise master-slave reading and writing chaos will occur.

　Note: The data synchronization work of the master-slave database is not implemented in the framework, and the database needs to consider its own synchronization or replication mechanism.

The above is the detailed content of ThinkPHP database operations stored procedures, data sets, distributed databases. For more information, please follow other related articles on the PHP Chinese website!