Tp5.1 framework database common operation details [add, delete, update, query]

Time:2020-9-25

This paper describes the common operations of tp5.1 framework database. For your reference, the details are as follows:

Tp5.1 — database add operation

useDb ClassinsertMethods submit data to the database


$data = ['foo' => 'bar', 'bar' => 'foo'];
Db::name('user')->insert($data);

insertMethod returns the number of successfully added data. Normally, it returns 1

Or usedataMethod coordinationinsertuse.


$data = ['foo' => 'bar', 'bar' => 'foo'];
Db::name('user')
  ->data($data)
  ->insert();

If it’s not in your data sheetfooperhapsbarField, then an exception is thrown.

If you don’t want to throw an exception, you can use the following method:


$data = ['foo' => 'bar', 'bar' => 'foo'];
Db::name('user')->strict(false)->insert($data);

The value of a nonexistent field will be discarded directly.

If it is a MySQL database, it supportsreplaceWrite, for example:


$data = ['foo' => 'bar', 'bar' => 'foo'];
Db::name('user')->insert($data, true);

After adding data, you can use theinsertGetIdMethod to add data and return the primary key value:


$userId = Db::name('user')->insertGetId($data);

insertGetIdMethod to add data successfully, return the data added from

Add multiple pieces of data

Add multiple data directly toDb ClassinsertAllMethod to pass in the data to be added


$data = [
  ['foo' => 'bar', 'bar' => 'foo'],
  ['foo' => 'bar1', 'bar' => 'foo1'],
  ['foo' => 'bar2', 'bar' => 'foo2']
];
Db::name('user')->insertAll($data);

insertAllMethod to add data successfully, return the number of successfully added data

If it is a MySQL database, it supportsreplaceWrite, for example:


$data = [
  ['foo' => 'bar', 'bar' => 'foo'],
  ['foo' => 'bar1', 'bar' => 'foo1'],
  ['foo' => 'bar2', 'bar' => 'foo2']
];
Db::name('user')->insertAll($data, true);

Add data

Add a piece of data

Using the insert method of DB class to submit data to the database


$data = ['foo' => 'bar', 'bar' => 'foo'];
Db::name('user')->insert($data);

insertMethod returns the number of successfully added data. Normally, it returns 1

Or usedataMethod coordinationinsertuse.


$data = ['foo' => 'bar', 'bar' => 'foo'];
Db::name('user')
  ->data($data)
  ->insert();

If it’s not in your data sheetfooperhapsbarField, then an exception is thrown.

If you don’t want to throw an exception, you can use the following method:


$data = ['foo' => 'bar', 'bar' => 'foo'];
Db::name('user')->strict(false)->insert($data);

The value of a nonexistent field will be discarded directly.

If it is a MySQL database, it supportsreplaceWrite, for example:


$data = ['foo' => 'bar', 'bar' => 'foo'];
Db::name('user')->insert($data, true);

After adding data, you can use theinsertGetIdMethod to add data and return the primary key value:


$userId = Db::name('user')->insertGetId($data);

insertGetId Method to add data successfully, return the auto increment primary key of the added data

Add multiple pieces of data

Add multiple data directly toDb ClassinsertAllMethod to pass in the data to be added


$data = [
  ['foo' => 'bar', 'bar' => 'foo'],
  ['foo' => 'bar1', 'bar' => 'foo1'],
  ['foo' => 'bar2', 'bar' => 'foo2']
];
Db::name('user')->insertAll($data);

insertAllMethod to add data successfully, return the number of successfully added data

If it is a MySQL database, it supportsreplaceWrite, for example:


$data = [
  ['foo' => 'bar', 'bar' => 'foo'],
  ['foo' => 'bar1', 'bar' => 'foo1'],
  ['foo' => 'bar2', 'bar' => 'foo2']
];
Db::name('user')->insertAll($data, true);

It can also be useddatamethod


$data = [
  ['foo' => 'bar', 'bar' => 'foo'],
  ['foo' => 'bar1', 'bar' => 'foo1'],
  ['foo' => 'bar2', 'bar' => 'foo2']
];
Db::name('user')->data($data)->insertAll();

Ensure that the data fields to be added in batch are consistent

If there is a large amount of data to be inserted in batch, you can specify batch insertion and use thelimitMethod to specify a limit on the number of inserts per insert.

$data = [
  ['foo' => 'bar', 'bar' => 'foo'],
  ['foo' => 'bar1', 'bar' => 'foo1'],
  ['foo' => 'bar2', 'bar' => 'foo2']
  ...
];
//Write up to 100 pieces of data at a time in batches
Db::name('user')->data($data)->limit(100)->insertAll();

Tp5.1 — database deletion

Delete data

//Delete according to primary key
Db::table('think_user')->delete(1);
Db::table('think_user')->delete([1,2,3]);
 
//Condition deletion  
Db::table('think_user')->where('id',1)->delete();
Db::table('think_user')->where('id','<',10)->delete();

The resulting SQL statement may be:


DELETE FROM `think_user` WHERE `id` = 1 
DELETE FROM `think_user` WHERE `id` IN (1,2,3) 
DELETE FROM `think_user` WHERE `id` = 1 
DELETE FROM `think_user` WHERE `id` < 10 

The delete method returns the number of pieces of data that affect the data. If it is not deleted, it returns 0

If called without any conditionsdeleteIf you really need to delete all the data, you can use the

//Delete all data unconditionally
Db::name('user')->delete(true);

The final generated SQL statement is (delete all the data of the table)


DELETE FROM `think_user`

In general, it is not recommended to delete business data in reality. The system provides a soft deletion mechanism (it is more convenient to use soft deletion in the model).

//Delete is used for soft deletion data_ Time field mark delete
Db::name('user')
 ->where('id', 1)
 ->useSoftDelete('delete_time',time())
  ->delete();

The actual generated SQL statement may be as followsUPDATEOperation:


UPDATE `think_user` SET `delete_time` = '1515745214' WHERE `id` = 1

useSoftDeleteMethod to indicate the use of soft deletion, and specifies that the soft delete field isdelete_time, write the data as the current timestamp.

Tp5.1 — database update operation

Update data


Db::name('user')
  ->where('id', 1)
  ->update(['name' => 'thinkphp']);

The actual generated SQL statement may be:


UPDATE `think_user` SET `name`='thinkphp' WHERE `id` = 1

updateMethod returns the number of items that affect the data. If no data is modified, 0 is returned

Support the use ofdataMethod passes in the data to be updated


Db::name('user')
  ->where('id', 1)
  ->data(['name' => 'thinkphp'])
  ->update();

IfupdateMethods anddataMethod at the same time, the merge is performed.

If the data contains a primary key, you can directly use:


Db::name('user')
  ->update(['name' => 'thinkphp','id'=>1]);

The actual generated SQL statement is the same as the previous usage:


UPDATE `think_user` SET `name`='thinkphp' WHERE `id` = 1

If the data to be updated needs to use SQL functions or other fields, you can use the following methods:


Db::name('user')
  ->where('id',1)
  ->inc('read_time')
  ->dec('score',3)
  ->exp('name','UPPER(name)')
  ->update();

Actual generated SQL statement:


UPDATE 
  `think_user` 
SET 
  `read_time` = `read_time` + 1 , 
  `score` = `score` - 3 , 
  `name` = UPPER(name) 
WHERE 
  `id` = 1

V5.1.7+After version, it supports the use ofrawMethod to update data, suitable for array update.


Db::name('user')
  ->where('id', 1)
  ->update([
    'name' => Db::raw('UPPER(name)'),
    'score' => Db::raw('score-3'),
    'read_time' => Db::raw('read_time+1')
  ]);

Update field values


Db::name('user')
  ->where('id',1)
  ->setField('name', 'thinkphp');

The resulting SQL statement may be as follows:


UPDATE `think_user` SET `name` = 'thinkphp' WHERE `id` = 1

setFieldMethod returns the number of items that affect the data, and returns 0 if no data field is modified

have access tosetInc/setDecMethod automatically increases or subtracts the value of a field (if the second parameter is not added, the default step size is 1).

//Add 1 to the score field
Db::table('think_user')
  ->where('id', 1)
  ->setInc('score');
//Score field plus 5
Db::table('think_user')
  ->where('id', 1)
  ->setInc('score', 5);
//Score field minus 1
Db::table('think_user')
  ->where('id', 1)
  ->setDec('score');
//Score field minus 5
Db::table('think_user')
  ->where('id', 1)
  ->setDec('score', 5);

The resulting SQL statement may be:


UPDATE `think_user` SET `score` = `score` + 1 WHERE `id` = 1
UPDATE `think_user` SET `score` = `score` + 5 WHERE `id` = 1
UPDATE `think_user` SET `score` = `score` - 1 WHERE `id` = 1
UPDATE `think_user` SET `score` = `score` - 5 WHERE `id` = 1

setInc/setDecDelay update is supported. If delay update is needed, the third parameter is passed in. In the following example, the update is delayed by 10 seconds.


Db::name('user')->where('id', 1)->setInc('score', 1, 10);

setInc/setDecMethod returns the number of items that affect the data. If delayed update is used, it may return true

Tp5.1 — database query operation

Selectorfail(); / / throw an exception if no data is found


Db::table('think_user')->where('status',1)->selectOrFail();

Findorfail(); / / throw an exception if no data is found


Db::table('think_user')->where('id',1)->findOrFail();

Findorempty(); / / returns an empty array when the query does not exist


Db::table('think_user')->where('id',1)->findOrEmpty();

Query the value of a field and return null when the result does not exist


Db::table('think_user')->where('id',1)->value('name');

Query the value of a column

//Return array
Db::table('think_user')->where('status',1)->column('name');
//Specify the value of the ID field as the index
Db::table('think_user')->where('status',1)->column('name','id');

Return complete data, and add an index value, query result does not exist, return an empty array

//Specifies the value of the ID field to return all data as an index
Db::table('think_user')->where('status',1)->column('*','id');

Returns a field value of a piece of data


Db::name('user')->where(['id' => 1])->value('name');

Data batch processing

All the user table data are processed in batches with 100 user records each time

Db::table('think_user')->chunk(100, function($users) {
  foreach ($users as $user) {
    //
  }
});
//Or leave it to the callback method myuseriterator
Db::table('think_user')->chunk(100, 'myUserIterator');

By returning from the closure functionfalseTo abort processing of subsequent data sets:

Db::table('think_user')->chunk(100, function($users) {
  foreach ($users as $user) {
    //Process result set
 if($user->status==0){
      return false;
    }
  }
});

Support inchunkMethod before calling other query methods, such as:


Db::table('think_user')
->where('score','>',80)
->chunk(100, function($users) {
  foreach ($users as $user) {
    //
  }
});

chunkBy default, the processing of the method is based on the primary key query and supports the specified fields, such as:

Db::table('think_user')->chunk(100, function($users) {
  //Process result set
  return false;
},'create_time');

And support to specify the order of processing data.

Db::table('think_user')->chunk(100, function($users) {
  //Process result set
  return false;
},'create_time', 'desc');

chunkThis method is usually used for command line operation of batch database data. It is not suitable for web access to process a large amount of data, and it is easy to cause timeout.

Mass data processing

If you need to process a large amount of data, you can use the cursor query function provided by the new version. This query method takes advantage of the generator features of PHP, which can greatly reduce the memory consumption of a large number of data queries.


$cursor = Db::table('user')->where('status', 1)->cursor();
foreach($cursor as $user){
 echo $user['name'];
}

cursorMethod returns a generator object, and the user variable is a piece of data (array) in the data table.

JSON type data query (MySQL)

//Query JSON type field (info field is JSON type)
Db::table('think_user')
 ->where('info->email','[email protected]')
 ->find();

More about ThinkPHP related content, interested readers can view this site topic: “ThinkPHP introduction tutorial”, “ThinkPHP template operation skills summary”, “ThinkPHP common methods summary”, “CodeIgniter introductory tutorial”, “Ci (CodeIgniter) framework advanced tutorial”, “Zend framework framework introduction tutorial” and “PHP template technology summary”.

I hope this article will be helpful to the PHP program design based on ThinkPHP framework.