MySQL implementation of lastInfdexOf function example

Sometimes MySQL needs to use a function similar to lastIndexOf, but it does not have a ready-made function directly available, so you need to figure it out yourself.

First, MySQL provides the following three functions:

instr(str:varchar, substr:varchar): Returns the first occurrence of subsrtr in str# For example: Find the first occurrence of b in the string ab/cd/abc.html select instr('ab/cd/abc.html', 'b');
# Unlike what the programmer thinks, the result above is 2 (the subscript starts at 1, please note)

reverse(str:varchar): reverse the string from beginning to end, input abc and output cba

length(str:varcha): Returns the length of the string

After knowing the above information, I think most people know how to implement the lastInfdexOf function.

Directly give:

select length('input string') - instr(reverse('input string'), 'string to be searched');
# eg Find the last position of '/' in the string 'ab/cd/abc.html'. The following results are: 5
select length('ab/cd/abc.html') - instr(reverse('ab/cd/abc.html'), '/');

Finally, here is a small requirement I encountered recently:

Write a SQL script to retrieve the value of the database field (the value is the path name of the file, such as ab/cd/abc.html) and parse the file name (abc.html):

select right(CONTENT_NAME, instr(reverse(CONTENT_NAME), '/') - 1) FILE_NAME
from config_template_content
where lower(CONTENT_NAME) like '%/%.html';
# This is enough when the data contains /. If the field value is abcde.html, you need to make a judgment~

The right function is used above. Please search (experience) its usefulness by yourself.

Supplement: Problems encountered when using mysql's SELECT LAST_INSERT_ID() statement

Preface:

When we insert a record into a database table whose primary key is auto-incremental (such as the user table), we expect to obtain the id of the record just written (because in some scenarios, we need to obtain this id to perform other operations). At this time, we need to use the SELECT LAST_INSERT_ID() statement to obtain the value of the auto-increment id returned after the record is inserted;

As an example:

 /**
 * Insert complaint information * @param complaintInfoModel
 * @return
 */
 @InsertProvider(type = ComplaintInfoDao.ComplaintProviderSql.class, method = "insertComplaintInfo")
 @SelectKey(statement="SELECT LAST_INSERT_ID()", keyProperty="id", before=false, resultType=Integer.class)
 Integer insertComplaintInfo(ComplaintInfoModel complaintInfoModel); // Insert data and return the number of modified database entries

SelectKey in Mybatis is to solve the problem that the primary key is not automatically generated when inserting data. It can set the method of generating the primary key at will. But no matter how good SelectKey is, try not to encounter this situation, after all, it is very troublesome.

selectKey Attributes

----1. SelectKey needs to pay attention to the order attribute,

In databases that support automatic growth, such as MySQL, order needs to be set to after to get the correct value.

In the case of taking a sequence like Oracle, it needs to be set to before, otherwise an error will be reported.

-----2. In addition, when using Spring to manage transactions,

SelectKey and insert are in the same transaction, so in this case, Mysql cannot get the automatically increased Key because the data is not inserted into the database.

Cancel transaction management and there will be no problem.

The following is an example of XML and annotations. The SelectKey is very simple, and two examples are enough:

<insert id="insert" parameterType="map"> 
 insert into table1 (name) values ​​(#{name}) 
 <selectKey resultType="java.lang.Integer" keyProperty="id"> 
  CALL IDENTITY() 
 </selectKey> 
 </insert>

The input parameter of the above xml is map, and selectKey will put the result into the input parameter map. The situation with POJO is the same, but one thing to note is that the field corresponding to the keyProperty must have a corresponding setter method in the POJO, and the parameter type of the setter must be consistent, otherwise an error will be reported.

@Insert("insert into table2 (name) values(#{name})") 
@SelectKey(statement="call identity()", keyProperty="nameId", before=false, resultType=int.class) 
int insertTable2(Name name);

The above is in the form of annotation.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.