laravel 5连接MySQL_Laravel5.5 MySQL配置、读写分离及操作

Laravel 让连接不同数据库以及对数据库进行增删改查操作:

参考：http://laravelacademy.org/post/854.html

配置读写分离

应用的数据库配置位于 config/database.php(但是数据库用户及密码等敏感信息位于 .env 文件)。在该文件中你可以定义所有的数据库连接，并指定哪个连接是默认连接。

'mysql' => [

'driver' => 'mysql',

'read' => [

'host' => ['39.107.243.232','39.106.201.242',]

],

'write' => [

'host' => '39.107.243.232'

],

'port' => env('DB_PORT', '3306'),

'database' => env('DB_DATABASE', 'test'),

'username' => env('DB_USERNAME', 'root'),

'password' => env('DB_PASSWORD', 'aaaa'),

'unix_socket' => env('DB_SOCKET', ''),

'charset' => 'utf8mb4',

'collation' => 'utf8mb4_unicode_ci',

'prefix' => '',

'strict' => true,

'engine' => null,

'modes' => [

'ONLY_FULL_GROUP_BY',

'STRICT_TRANS_TABLES',

'NO_ZERO_IN_DATE',

'NO_ZERO_DATE',

'ERROR_FOR_DIVISION_BY_ZERO',

'NO_ENGINE_SUBSTITUTION',

],

],

Laravel 在读数据时会从提供的 IP 中随机选一个进行连接。目前Lavavel5.5读写分离仅支持单个写连接。

使用不同数据库连接:

1. 原生SQL：

$id = 2;

$users = DB::connection('mysql::read')->select('select * from users where id = ?',[$id]);

if( empty($users) )

{

print("Record is not exist : id = $id");

return;

}

$users = json_decode(json_encode($users),true);

$user = $users[0];

print($user['name']);

$id = 1;

$user['name'] = 'abce21s';

$affected = DB::update('update users set name=? where id=?', [$user['name'],$id]);

if(true != $affected)

{

print("Update data failed! : id = $id");

return;

}

print('Update data successful!');

$id = 2;

$user['name'] = 'aaaa';

$affected = DB::insert('insert into users (id, name) values (?, ?)', [$id, $user['name']]);

if(true != $affected)

{

print("Update data failed! : id = $id");

return;

}

print('Update data successful!');

可以强制写到读或者写的数据库中：

$id = 1;

$user['name'] = 'ab1s';

$affected = DB::connection('mysql::read')

->update('update users set name=? where id=?', [$user['name'],$id]);

if(true != $affected)

{

print("Update data failed! : id = $id");

return;

}

print('Update data successful!');

$id = 1;

$user['name'] = '111';

$affected = DB::connection('mysql::write')

->update('update users set name=? where id=?', [$user['name'],$id]);

if(true != $affected)

{

print("Update data failed! : id = $id");

return;

}

print('Update data successful!');

2. 查询构建器：

$id = 2;

$user['name'] = 'abcde2';

$affected = DB::connection('mysql::read')

->table('users')

->where('id', $id)

->update(['name' => $user['name']]);

if(true != $affected)

{

print("Update data failed! : id = $id");

return;

}

print('Update data successful!');

$id = 1;

$user['name'] = '222';

$affected = DB::connection('mysql::write')

->table('users')

->where('id', $id)

->update(['name' => $user['name']]);

if(true != $affected)

{

print("Update data failed! : id = $id");

return;

}

print('Update data successful!');

$id = 2;

$user['name'] = 'bbb';

$affected = DB::connection('mysql::write')

->table('users')

->insert(['id'=> $id, 'name'=>$user['name']]);

if(true != $affected)

{

print("Update data failed! : id = $id");

return;

}

print('Update data successful!');

3. Eloquent ORM

参考：https://docs.golaravel.com/docs/5.5/eloquent/

http://laravelacademy.org/post/8060.html