What does the update statement in mysql return?

The return result of the update statement in mysql: 1. When there is no "useAffectedRows=true" parameter in the database URL, the number of matching rows is returned; 2. When there is the "useAffectedRows=true" parameter in the database URL , returns the number of affected rows.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

What does the update statement in mysql return?

What is the number of matching rows that the result of the update statement returns? Or does it affect the number of rows?

Let’s talk about the results first: If the url of the database is:

jdbc:mysql://gitlab.fzghjx.com:3306/cron

, then the returned result is number of matching rows (Rows matched).

If it is:

jdbc:mysql://gitlab.fzghjx.com:3306/cron?useAffectedRows=true

, the number of affected rows (Changed) is returned.

To sum up:

If there is no useAffectedRows=true parameter in the url, the number of matching rows will be returned. If so, the number of affected rows is returned.

How to make its return value be the number of affected (changed) records?

Just add useAffectedRows=true to the mysql data connection url parameter

jdbc.url=jdbc:mysql://localhost:3306/ssm?useAffectedRows=true

Expand knowledge:

Guess

If you operate the update statement of mysql through cmd, the screen display is actually like this:

When I think about this problem, first There are two answers to the response. 1. Make a judgment in the return result of the mysql server. If this is set to true, the value of Rows matched is returned. If it is false, the value of Changed is returned. 2. Select this value when returning to the query statement.

Confirmation

Let’s use a diagram to illustrate the entire process of creating a connection:

(Picture taken from: https://blog.csdn.net/c929833623lvcha/article/details/44517245)

After I studied for a while, I found that these two ideas All wrong. Specifically:

I wrote a simple jdbc query:

1, establish Connection

According to the source code and tracking, when mysql establishes a connection, the parameter useAffectedRows=true will be set as the connection attribute.

Follow down in

Connection connection = DriverManager.getConnection("jdbc:mysql://gitlab.fzghjx.com:3306/cron?useAffectedRows=true", "root", "root");

:

Continue: The connect method is: java.sql.Driver#connect, and the implementation is: com.mysql.jdbc.NonRegisteringDriver#connect

##useAffectedRows=true is read in the com.mysql.jdbc.NonRegisteringDriver#parseURL method and set to Properties props of.

Further down: com.mysql.jdbc.ConnectionImpl#getInstance to get the instance of connectiong

##Here is a reflection, args is

JDBC_4_CONNECTION_CTOR

The constructor of JDBC is used:

#The constructor of JDBC is:

public JDBC4Connection(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) throws SQLException {
    super(hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url);
}

Look up, it is the constructor of ConnectionImp:

(By the way: The isolation level of the transaction is set to 2 here, and the read is committed)

In In this constructor, the value of useAffectedRows is initialized into the connection:

There are 206 properties to be set, useAffectedRows is ranked 190th (different mysql-connect -java version, location is different).

After the settings are completed:

##2, create MysqlIO

After setting the properties, it is time to create MysqlIO:

There is a "high availability" option. If so, a retry IO link will be created. Otherwise, create an IO link that is tried only once, and will not be retried if it fails. This option is also enabled in the URL by setting: autoReconnect=true.

3, handshake connection with msyql server

The connection IO process is actually to create a MysqlIO and then start the handshake:

The key points Already: In the doHandshake method, the useAffectedRows option is set: (com.mysql.jdbc.MysqlIO#doHandshake)

##The value of this CLIENT_FOUND_ROWS is:

That is, the value of the penultimate digit of the low bit performs

or operations. If useAffectsRows, no or operations will be performed.

After setting, send it to the mysql server through the mysqlOutput socket:

mysqlOutput source: Created when MysqlIO is built.

ok, this is a simple process. Next, let’s look at the tcp message:

4, message analysis:

useAffectedRows=true:

Send request To mysql server:

Response from mysql server:

## When #useAffectedRows=false:

Send a request to the mysql server:

##Mysql server’s response:

As can be seen from the above message, when useAffectedRows is true and false, the return value of the msyql server is different

. Finally, the return result obtained by jdbc is the value of Affected Rows in the message. Obtained directly from the results of msyql, jdbc just performs some parsing and filtering on the results. This proves that the initial guess was wrong.

Recommended learning: mysql video tutorial

The above is the detailed content of What does the update statement in mysql return?. For more information, please follow other related articles on the PHP Chinese website!