It becomes like sitting in a well and looking at the sky. A new company I joined is a company that makes enterprise management software. This is really different from the company I used to work in that was an Internet e-commerce company. I just started working, and it is obvious that the sql used by the program is
It becomes a lot more complicated. The sql of the management software is really long. It starts with 200 rows and can reach 2000 rows. It makes my scalp numb when I look at it. The sql for e-commerce programs is much simpler. For single tables, you don’t need to write sql yourself. It’s a little complicated.
Even the complicated stuff can be done within a hundred lines. But how can this problem stump me, who has been through the battlefield for a long time? As long as the SQL format is beautifully written, I can still read it slowly. Quickly start the built-in formatting of idea, press ctrl+alt+L:
The result surprised me. The SQL that I could still read was formatted like this and I couldn't stand it anymore. There were large blank lines, inexplicable code block offsets (indentations), and too few valid statements per line. The number of rows has increased dramatically.
It won’t work! If Idea is formatted like this, it can only be formatted in database management software. I generally support the genuine version and usually use DBeaver, an open source and free software, so let's get started.
This looks much more comfortable at first glance. The case part can be seen as a piece-by-piece structure, which makes the logic much clearer. However, when I scrolled down a little, I saw this content:
In this part of the formatting, there are a lot of and and or operations, functions and parentheses pairs. The style of the statements immediately becomes a bit like the ones in the idea. The lines are too short and there is no complete block semantics, making it difficult to understand. These sentences are doing
What's wrong? Even if you try hard to read one or two lines clearly, when you go to the next section, you can't find where the previous section begins and where it ends. Especially when you are looking attentively, the guy sitting next to you pushes you
Say: "Look at this video on Douyin, it's really funny. It's so funny!" In this way, when you come back after dealing with it, you have to start analyzing the Sql again. I just want to say: "Who am I and where am I?".
The two tricks I usually use can't solve the problem of large Sql, so I have to use the third trick. Cheng Yaojin still has three axes. I can't give in with just two tricks. Idea has a plug-in, so go find the plug-in. If you can find the symbol in the idea
Tasteful plug-ins are much more convenient than database management software! Just do it. Search for "sql format" on the plug-in to see which plug-in is used by more people and has a higher rating. Just get it and try it out first!
When I entered sqlf, there were only three entries left in the market. This was beyond my expectation. I thought there were many similar plug-ins, but the actual situation was that there were only three, and two of them had the same name. , one free
One charge. Forget it, I'll just try "SQLFormatter" first. The name seems to match. As shown in the first picture, it looks like it has been installed when I wrote this document. The plug-in has been updated. It seems that this
The plugin is still being actively updated, which is a good thing! Let’s take a look at how it looks formatted:
Compared with the formats formatted by the previous two tools, the front part is clearly divided into blocks, and the lower part is obviously compact. A large number of and and or operations are arranged in one line, so that one logical result can be viewed at a time. Will come out
There are frequent line breaks. Line breaks too frequently make it feel like listening to someone speak before they finish a sentence and then change to the next sentence. This basically suits my style. The similar and repeated parts of the statements should be grouped into blocks. It is simple and many.
The operations are arranged in lines, and one logic can be read at a time. The number of lines is not too many, and it is basically usable. Programmers have very high requirements!
By the way, after installing the plug-in, use it as shown in the figure, right-click on the area that needs to be formatted, and select "format sql" at the bottom.
Next, I will find a few SQLs from the project to try this plug-in. If most of them can agree with me, then this plug-in will be the one from now on, and there is no need to make new doctrines. The left side of the screenshot below is the grid built into the idea.
Formatting, the right side is the formatting of SQLFormatter. I still compare it and take a look. Although the built-in formatting has been denied by me, the built-in formatting has more users. The most convincing comparison with the built-in formatting is still comparison.
Compare it!
The effect of the built-in formatting this time is not much different from that of the SQLFormatter plug-in. There are two differences: 1. The indentation of the built-in formatting is larger, while the indentation of the SQLFormatter plug-in is smaller. 2. The built-in plug-in attempts to perform global alignment of as field names.
But in fact, due to the limitation of row width, alignment was later abandoned. The SQLFormatter plug-in wrapped the as field name in a new line, and the row width was enough to stay on the same line. In fact, you can see that I deliberately changed the built-in formatting window
The window is enlarged because the field name cannot be displayed without enlarging it. You can't see it when taking a screenshot, and I can't see it when using it. It's a bit useless and requires sliding the window left and right.
The difference in formatting indentation is even more obvious this time. The indentation no longer feels like "indentation", but rather a bit confusing.
This picture may not look like the formatting of the same SQL at first glance, but let me tell you seriously, it is actually the formatting of the same SQL. Pay attention to the horizontal scroll bar at the bottom of the left window, and you will understand. Built-in formatting
The select field name in the first line is not line-wrapped, and there are too many fields in this query, so the first line of built-in formatting is very long. SQLFormatter has line-wrapped and aligned the query fields, which is great. Scroll horizontally to see
Look at the picture below, there is a long way after the first line!
Post the formatting comparison chart again
I will not explain and compare this one by one. In fact, it is similar to the above, except for the exceptions above indentation and line breaks.
When it comes to formatting, what should be compared is the indentation and line breaks!
At the end of writing, I would like to officially announce that I am quite satisfied with the formatting tool SQLFormatter and will choose it in the future. Other than that, this tool still has optimization points, and there is also a screenshot. I will give feedback to the author and slowly evolve it!
It’s not easy to find a new job. I’d better do less work that has nothing to do with the boss. I’ll quickly save a draft and get up to see who needs help. I’ll go and do some extra work.
the, formatting, and, plug-in
