I’ve been using the MYSQL database quite a bit in the last couple of years, and I’ve found some very useful gadgets, so I’m taking them out and sharing them with you today in the hopes that they’ll be helpful.
1.group_concat
The scenarios in which we use group by for grouping in our normal work, are numerous.
For example, you want to find out what are the specific names of users with different names in the user table?
The specific sql is as follows:
select name from `user`
group by name;
But what if you want to splice together codes with the same name and put them in another column?
A: Use the group_concat function.
select name,group_concat(code) from `user`
group by name;
Result: Using the group_concat function, you can easily group the data with the same name together to form a string, separated by .
2.char_length
Sometimes we need to get the of a character and then based on the length of the character.
MYSQL gives us some useful functions such as char_length .
With this function it is possible to get the character length.
The sql to get the length of the characters and sort them is as follows:
select * from brand where name like '%1%'
order by char_length(name) asc limit 5;
The result is shown in the figure: after using the keyword for the name field, use the char_length function to get the length of the characters in the name field, and then press for the length.
3.locate
What should we do sometimes when we are looking for a keyword, e.g. 1 , and need to know exactly where it is in a string?
A: Use the locate function.
After using the locate function the sql is transformed as follows:
select * from brand where name like '%1%'
order by char_length(name) asc, locate('1',name) asc limit 5,5;
The result: first sort by length, the smaller in front. If the length is the same, then the keywords are sorted from left to right, the more to the left the more in front.
In addition, we can also use: instr and position functions, their functions are similar to the locate function, I will not introduce them here, interested partners can find me private chat.
4.replace
We often have the need to replace parts of a string, for example: replace the character A with B in the string.
The replace function can be used in this case.
update brand set name=REPLACE(name,'A','B')
where id=1;
This makes it easy to implement the character replacement function.
You can also remove with this function:
update brand set name=REPLACE(name,' ','') where name like ' %';
update brand set name=REPLACE(name,' ','') where name like '% ';
Using this function also replaces the data content of json , which is really useful.
5.now
Time is a good thing to use to quickly narrow down data, and we often have a need to get the current time.
To get in MYSQL, you can use the now() function, for example:
select now() from brand limit 1;
The return result will look like this: it will contain .
If you also want to return , you can use now(3) , for example:
select now(3) from brand limit 1;
The return result is something like the following: It is very easy to use and remember.
6.insert into … select
There are many times when you need at work.
The traditional sql for inserting data looks like this:
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (5, '108', '1', '2022-09-02 19:42:21');
It is mainly used to insert a small amount of data that has already been determined. However, if there is a large amount of data to be inserted, especially if the data to be inserted comes from the result set of another table or multiple tables.
In this case, using the traditional way of inserting data is a bit of a handful.
This is where you can use the MYSQL provided: insert into ... select syntax.
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
select null,code,name,now(3) from `order` where code in ('004','005');
This makes it very easy to insert some of the data from the ORDER table into the BRAND table.
7.insert into … ignore
I don’t know if you have ever encountered such a scenario: before inserting 1000 brands, you need to determine whether they exist or not, based on the name. If it exists, no data is inserted. If it doesn’t exist, then you need to insert the data.
If the data is inserted directly like this:
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (123, '108', '1', now(3));
It won’t work because the name field of the brand table has a unique index created, and there is already a row in the table with a name equal to Su San.
After execution, it reports an error directly: this requires a little judgment before insertion.
Of course many people achieve the goal of preventing duplicate data from appearing by splicing the not exists statement after the sql statement, for example:
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
select null,'108', '1',now(3)
from dual where not exists (select * from `brand` where name='1');
This sql does fulfill the requirements, but it’s always a bit of a pain in the ass. So, is there a simpler way to do it?
A: You can use the insert into ... ignore syntax.
INSERT ignore INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (123, '108', '1', now(3));
After this transformation, if there is no data in the brand table whose name is Su San, it can be directly inserted successfully.
However, if the data with the name Su San already exists in the brand table, the sql statement can be executed normally and will not report an error. Because it will ignore the exception, the returned execution result affects the number of rows is 0, it will not repeat the insertion of data.
I’ve recently built a new tech exchange group and intend to make it a high quality active group, so guys are welcome to join.
My past tech groups have had a great tech atmosphere with lots of bigwigs.
Add micro letter: su_san_java, note: add group, you can join the group.
8.select … for update
In our actual business scenarios, there are some cases where the concurrency is not too high, and it is okay to use pessimistic locks in order to ensure the correctness of the data.
For example: the user deducts points, the user’s operation is not centralized. However, it is also necessary to consider the concurrency of the system to automatically give away the points, so it is necessary to add a pessimistic lock to limit it, to prevent the occurrence of points added to the wrong situation.
This is the time to use the select ... for update syntax in MYSQL.
begin;
select * from `user` where id=1
for update;
update `user` set score=score-1 where id=1;
commit;
This way, if you lock a row in a transaction using for update , no other transaction can update that row until that transaction commits.
It is important to note that the id condition before the for update must be of the table, otherwise the row lock may not work and it may become .
9. on duplicate key update
Typically, before we insert data, we usually query whether the data exists or not. If it does not exist, then insert the data. If it already exists, the data is not inserted and the result is returned directly.
In scenarios where there is not much concurrency, there is nothing wrong with this practice. However, if the request to insert data, has a certain amount of concurrency, this practice may generate duplicate data.
Of course there are many ways to prevent duplicate data, such as: etc.
However, none of these solutions can do anything to update the data for the second request, and they will usually just return it when they determine that it already exists.
The on duplicate key update syntax can be used in this case.
This syntax determines before inserting the data, if the primary key or unique index does not exist. If the primary key or unique index exists, the update operation is performed.
The specific fields to be updated can be specified, for example:
INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (123, '108', 'John', now(3))
on duplicate key update name='John',edit_date=now(3);
This makes it easy to take care of the requirements with a single statement that doesn’t create duplicate data and also updates the latest data.
However, it should be noted that using the on duplicate key update syntax in highly concurrent scenarios may have problems with , so use it with discretion depending on the actual situation.
10.show create table
Sometimes we want to have a quick look at the status of a field in a particular table, and we usually use the desc command, for example:
desc `order`;
The result is shown in the figure: you can indeed see the field names, field types, field lengths, whether they are allowed to be empty, whether they are primary keys, default values, etc. in the order table.
But I can’t see the index information of the table, what should I do if I want to see which indexes are created?
A: Use the show index command.
比如：
show index from `order`;
I can also find out all the indexes of the table: but it’s always a bit weird to see how the field and index data is presented, is there a more intuitive way?
A: This requires the use of the show create table command.
show create table `order`;
The result is shown in the figure: where Table means , Create Table is what we need to look at , the data will be expanded: we can see a very complete statement to build the table, the table name, field names, field types, field lengths, character sets, primary keys, indexes, the execution engine and so on can be seen.
Very straightforward and clear.
11.create table … select
Sometimes we need to backup tables quickly.
Typically, this can be done in two steps:
- Create a temporary table
- Inserting data into a temporary table
To create a temporary table you can use the command:
create table order_2022121819 like `order`;
After the successful creation, it will generate a name: order_2022121819, the table structure is exactly the same as the order of , only the table and has been.
Next use the command:
insert into order_2022121819 select * from `order`;
After execution it will insert the data from the order table into the order_2022121819 table, that is, it will realize the function of data backup.
But is there a command, a single command that does both of these steps above?
A: Use the create table ... select command.
create table order_2022121820
select * from `order`;
After execution, the order_2022121820 table is created and the data from the order table is automatically inserted into the newly created order_2022121820.
It’s easy with one command . I have recently built a new technical communication group, and I intend to make it a high-quality and active group, so guys are welcome to join.
My past tech groups have had a great tech atmosphere with lots of bigwigs.
Add micro letter: su_san_java, note: add group, you can join the group.
12. explain
Many times, we optimize the performance of a sql statement and need to look at execution.
A: You can use the explain command to view mysql at and it will show
explain select * from `order` where code='002';
Through these columns can determine the use of indexes, the execution plan contains the meaning of the columns are shown in the following figure: If you want to learn more about the detailed use of explain, you can look at my other article “explain | Index Optimization of this great sword, you really know how to use it?
To be honest, the sql statement didn’t go to the index, excluding the fact that no index was built, the most likely reason is that the index failed.
Here are some common reasons why indexing fails: If it’s not one of these reasons, then you need to look further into other reasons.
13.show processlist
There are times when we have problems with our online sql or database. For example, there is a problem with too many database connections, or you find that one sql statement takes an unusually long time to execute.
What to do at this point?
A: We can use the show processlist command to view
As shown in the figure: From the execution results, we can view the current connection status to help identify problematic query statements.
- id thread id
- User The account that executes the sql.
- Host The ip and terminal number of the database from which the sql is executed.
- db Database name
Command Execute commands, including: Daemon, Query, Sleep, etc.- Time The time consumed to execute the sql
- State Execution state
- info Execution information, which may contain sql information.
If you find an abnormal sql statement, you can just kill it to make sure that the database does not have serious problems.
14. mysqldump
Sometimes we need to export data from a MYSQL table.
In this case you can use the mysqldump tool, which looks up the data, converts it into an INSERT statement, and writes it to some file, equivalent to
We get that file and then execute the appropriate INSERT statement to create the relevant table and write the data, which is equivalent to
Back up the database in the remote database:
mysqldump -h 192.22.25.226 -u root -p123456 dbname > backup.sql
After a year of hard work, I’ve finally made it to this year’s Outstanding Authors list, so I’m begging for votes, and I’m begging for votes, so each person can vote for 8. I’d like to thank you very much for your support.