Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the result returned by the update statement in mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "what is the result returned by the update statement in mysql"!

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

Operating environment of this tutorial: Windows 10 system, mysql version 8.0.22, Dell G3 computer.

What is the number of matching rows returned by the update statement in mysql? Or does it affect the number of rows?

First, if the URL of the database is:

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

Then the return result is Rows matched.

If:

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

The number of affected rows (Changed) is returned.

Summary:

If the url does not have useAffectedRows=true, the number of matching rows is returned. If yes, the number of affected rows is returned.

How do you get the return value to be the number of records changed?

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

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

Extended knowledge:

conjecture

If you operate mysql update statement through cmd, the screen shows this:

When I think about this question, the first reaction has two answers, 1, in the mysql server return results, to determine, if there is this set to true, then return the value of Rows matched, if false, then return the value of Changed. 2. Select this value when returning to the query statement.

confirmed

To illustrate the whole process of creating a connection:

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

After studying it for a while, I realized that both ideas were wrong. Specifically:

I wrote a simple jdbc query:

1, establish a Connection

According to the source code, trace, when mysql establishes a connection, the parameter useAffectedRows=true will be set to the connection property.

in

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

Track down:

Continue: connect method: java.sql.Driver#connect, implementation: com.mysql.jdbc.NonRegisteringDriver#connect

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

Further down: com.mysql.jdbc.ConnectionImpl#getInstance Get an instance of connectiong

Here is a reflection, args is JDBC_4_CONNECTION_CTOR using the JDBC constructor:

The JDBC constructor is:

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

Looking up, it is the constructor of ConnectionImp:

(Incidentally, the isolation level of the transaction is set to 2 here, and the read has been committed)

In this constructor, the useAffectedRows value is initialized to the connection:

There are 206 properties to set, useAffectedRows at 190 bits (different mysql-connect-java versions, different locations).

After the setup is complete:

2. Create Mysql IO

After setting the properties, you need to create Mysql IO:

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

3. Shake hands with msyql server

The process of connecting IO is actually to create a Mysql IO and then start shaking hands:

The key point is: in the doHandshake method, the useAffectedRows option is set: (com.mysql.jdbc.MysqlIO#doHandshake)

The CLIENT_FOUND_ROWS value is:

That is to say, the value of the penultimate digit of the low order is OR operated. If useAffectsRows, no OR operation is performed.

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

mysqlOutput Source: Created when MysqlIO was built.

OK, this is a simple process. Next, let's look at the TCP message:

4. Message analysis:

When useAffectedRows=true:

Send request to mysql server:

Response from MySQL Server:

When useAffectedRows=false:

Send request to mysql server:

Response from MySQL Server:

As can be seen from the above message, when useAffectedRows is true and false, the return value of msyql server is different. Finally, the return result obtained by jdbc is the value of Affected Rows in the message. Taken directly from msyql's results, jdbc just does some parsing and filtering on the results. This proves that the initial assumption is wrong.

At this point, I believe that everyone has a deeper understanding of "what is the result returned by the update statement in mysql", so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.