This plug-in can access both a normal MySQL database and an online database service based on the MySQL protocol.

Project configuration

  1. Mysql installation

MySQL Error 1042: Unable to connect to any of the specified MySQL hosts

MySQL80 is the default name of MySQL 8.0. Double-click MySQL80 to open it. Under login TAB, change the selected account to local system account.

  1. Install the following plugin:
$ npm i --save egg-mysql
Copy the code
  1. Open the plug-in
// config/plugin.js
exports.mysql = {
  enable: true.package: 'egg-mysql'};Copy the code
  1. Configure database connection information for each environment in config/config.${env}.js
 config.mysql = {
    client: {
      host: '127.0.0.1'.port: '3306'.user: 'root'.password: '12345678'.database: 'exam',},// Whether to load to app, enabled by default
    app: true.// Whether to load it to the agent
    agent: false};Copy the code

Problems encountered in starting the project

Problem 1. Client does not support authentication protocol requested by server; consider upgrading MySQL client

> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678';

> flush privileges;
Copy the code

Error 2. Access denied for user’root’@’localhost’ (using password: NO)

Since there is no password set in the config.mysql configuration, the setting is correct

Operations on the database

Get the query

  • Query a single
const user = await this.app.mysql.get('data_person', {title: 'Hello World'});
Copy the code
  • Query the full table
const user = await this.app.mysql.get('data_person');
Copy the code

The test results in the first matching data

Select * from (select * from (select * from (select * from (select * from))))

 const user = await this.app.mysql.select('data_person', { // Search for the POST table
  where: { title: 'Hello World'.age: [10.11]},/ / the WHERE condition
  columns: ['title'].// The table field to query
  orders: [['person_id'.'desc']], // Sort
  limit: 10.// Return the amount of data
  offset: 0.// Data offset
});  

=> SELECT `author`.`title` FROM `posts`
  WHERE `title` = 'Hello World' AND `age` IN(10.11)
  ORDER BY `person_id` DESC,  LIMIT 0.10;
Copy the code

Insert Insert data

await this.app.mysql.insert('data_person', { title: 'Hello World' });
Copy the code

Update Update data

Modify data, which will be looked up by primary key and updated. Otherwise, an error will be reported

Can not auto detect update condition, please set options. Where, or make sure obj.id exists

// Modify the data, will be based on the primary key ID, and update
const row = {
  id: 6.title: 'Not primary key'.create_time: this.app.mysql.literals.now,
};
const result = await this.app.mysql.update('posts', row); 

=> UPDATE `data_person` SET `title` = 'Not primary key'.`create_time` = NOW() WHERE id = 6 ;
Copy the code
const row = {
  title: 'Update primary key'.create_time: this.app.mysql.literals.now, 
};

// If the primary key is a custom ID name, such as person_id, it needs to be configured in 'where'
const options = {
  where: {
    person_id: '6'}};const result = await this.app.mysql.update('data_person', row, options); 

=> UPDATE `data_person` SET `title` = 'Update primary key'.`create_time` = NOW() WHERE person_id = '6' ;

// Check that the update is successful
const updateSuccess = result.affectedRows === 1;
Copy the code

Delete Delete data

const result = await this.app.mysql.delete('data_person', {
  person_id: '5'
});

=> DELETE FROM `data_person` WHERE `person_id` = '5';

// delete all person_id '5'
Copy the code

Query executes the SQL statement directly. To prevent SQL injection, use each? How to match an element

const personId = '6';
const results = await this.app.mysql.query('update data_person set age = (age + ?) where person_id = ? '[5, personId]);

=> update data_person set age = (age + 5) where person_id = '6';
Copy the code

transaction

MySQL transactions are mainly used to process data with large operation volume and high complexity. For example, in the personnel management system, if you delete a person, you need to delete both the basic information of the person and the information related to the person, such as mailbox, articles and so on. Using transactions at this point makes it easy to manage this set of operations. Transactions are a group of consecutive database operations performed in a single unit of work. For each individual operation within the group to succeed, the transaction succeeds. If any operation in the transaction fails, the entire transaction fails.

Support transaction conditions: In MySQL, only databases or tables using Innodb database engine are supported.

Generally speaking, transactions must meet 4 conditions: Atomicity, Consistency, Isolation, reliability.

  • Atomicity: Ensures that all operations within a transaction complete successfully, otherwise the transaction is aborted at the point of failure and previous operations are rolled back to their previous state.
  • Consistency: Changes to the database are consistent.
  • Isolation: Transactions are independent of each other and do not affect each other
  • Persistence: Ensures that after a transaction is committed, the results of the transaction are permanent.

Therefore, a transaction must be accompanied by beginTransaction, COMMIT, or rollback, which respectively represent the start of the transaction and the rollback of success and failure.

Egg-mysql provides two types of transactions:

  1. Manual control

Advantages: beginTransaction, COMMIT or ROLLBACK are fully controlled by the developer and can achieve very fine-grained control.

Disadvantages: handwritten code is more, not everyone can write well. Forgetting to catch exceptions and cleanup can lead to serious bugs.

const conn = await app.mysql.beginTransaction(); // Initialize the transaction

try {
  await conn.insert(table, row1);  // First step
  await conn.update(table, row2);  // Step 2
  await conn.commit(); // Commit the transaction
} catch (err) {
  // error, rollback
  await conn.rollback(); // Roll back the transaction after catching the exception!!
  throw err;
}
Copy the code
  1. Automatic control: Transaction with Scope
API: beginTransactionScope (scope, CTX)Copy the code

Scope: A generatorFunction that executes all SQL statements for this transaction.

CTX: The context object of the current request. Passing in CTX ensures that there is only one active transaction at a time in a request, even in the case of transaction nesting.

Advantages: simple to use, not easy to make mistakes, the feeling of transaction does not exist.

Disadvantages: The entire transaction either succeeds or fails, without fine-grained control.

const result = await app.mysql.beginTransactionScope(async conn => {
  // don't commit or rollback by yourself
  await conn.insert(table, row1);
  await conn.update(table, row2);
  return { success: true };
}, ctx); // CTX is the context of the current request, which can be obtained from 'this.ctx' if it is in a service file
// if error throw on scope, will auto rollback
Copy the code

Built-in and custom expressions (Literal)

If you need to call MySQL’s built-in functions (or expressions), you can use Literal.

  • built-in

NOW() : indicates the current system time of the database, which can be obtained from app.mysql.literals.now.

 await this.app.mysql.insert('data_person', {
    person_id: '8'.create_time: this.app.mysql.literals.now,
  });

=> INSERT INTO `data_person`(`person_id`.`create_time`) VALUES('8',NOW())
Copy the code
  • The custom

The following example shows how to call the MySQL built-in CONCAT(s1,… Sn) function, do string concatenation.

const Literal = this.app.mysql.literals.Literal;
const first = "he";
const last = "hannie";
await this.app.mysql.insert('data_person', {
  person_id: '7'.title: new Literal(`CONCAT("${first}","${last}") `),  // Pay attention to syntax, don't omit parentheses, etc
});

=> INSERT INTO `data_person`(`person_id`.`title`) VALUES(7, CONCAT("he"."hannie"))
Copy the code

conclusion

Insert, UPDATE, DELETE, slect, and so on must have corresponding fields