SQL Query Formatting Tools Used At Percona

栏目: IT技术 · 发布时间: 4年前

内容简介:Online query formatting services provide one set of solutions. Examples areThis article examines alternatives to online tools for SQL query formatting tools that have been successfully used by Percona engineers. These solutions come in different types:

SQL Query Formatting Tools Used At Percona Percona engineers often need to analyze and review complex SQL database queries. Good formatting can make these SQL queries much easier to understand and work with. Without clear formatting, queries can become confusing and hard to debug.

Online query formatting services provide one set of solutions. Examples are Code Beautify , FreeFormatter , and sqlformat.org . However, many users are not comfortable sharing their queries with third-party services, especially if your SQL code contains confidential information.

This article examines alternatives to online tools for SQL query formatting tools that have been successfully used by Percona engineers. These solutions come in different types:

  1. Plug-ins to your code editor or IDEs.
  2. Database Management tools
  3. Terminal Tools

Let’s examine these options.

Code editor or IDE

Most modern code editors have functionality or plug-ins for formatting queries. We checked Visual Studio Code and Sublime because these popular code editors are available for Linux, Mac, and Windows.

Visual Studio Code knows how to format SQL queries, as well as available Extensions for working with databases, for example, “SQLTools – Database tools” . Sublime does not have SQL formatting as standard functionality, but it is easy to add it through Package Control. Several packages are available for Query formatting, and we used Sql​Beautifier .

In this case, you need to copy the request to the code editor and save it to a file. It turned out that most of the Percona team use console tools.

SQL Query Formatting Tools Used At Percona

SQL Query Formatting Tools Used At Percona

DBA tools

Special database management and monitoring software also have functionality for query formatting.

MySQL Workbench

MySQL Workbench enables a DBA or developer to visually, generate, and manage databases.

The Workbench allows you to format an SQL query into a new SQL tab and click the “Beautify/reformat the SQL script” button.

SQL Query Formatting Tools Used At Percona

Percona Monitoring and Management (PMM)

Percona Monitoring and Management (PMM) is a free, best-of-breed, open-source database monitoring and management solution. PMM has the useful functionality of PMM Query Analytics, which allows you to immediately view requests in formatted form.

SQL Query Formatting Tools Used At Percona

Terminal tools

The most popular console tool for formatting queries in our team is sqlparse (sqlformat). The developers of the popular online service SQLFormat were very kind and provided the source code of their excellent tool (sqlparse). This simple and useful tool you can use on different platforms.

Docker

If you love Docker and do not want to install the tool on your system. Percona Support Engineer Agustín Gallego has packed the data script into a docker. Here is a simple instruction on how you can use it.

shell> docker pull guriandoro/sqlparse:0.3.1
shell> docker run --rm \
>   --network=none \
>   guriandoro/sqlparse:0.3.1 "SELECT several, columns from a_table as a join another_table as b where a.id = 1;"
SELECT several,
       columns
FROM a_table AS a
JOIN another_table AS b
WHERE a.id = 1;

Cool, right?

Ubuntu

It’s very simple, install the sqlformat package.

# apt install sqlformat
 
$ echo "SELECT several, columns from a_table as a join another_table as b where a.id = 1;" | sqlformat - -r -k upper
SELECT several,
       columns
FROM a_table AS a
JOIN another_table AS b
WHERE a.id = 1;

Mac

Install sqlparse using brew or another way to use the command sqlformat.

# brew install sqlparse
 
# echo "SELECT several, columns from a_table as a join another_table as b where a.id = 1;" |  sqlformat - -r -k upper
 
SELECT several,
       columns
FROM a_table AS a
JOIN another_table AS b
WHERE a.id = 1;

VIM

And if you love and use VIM, you can also install sqlformat and use it.

vmap <leader>sql :%!sqlformat - -r -k upper<CR>

SQL Query Formatting Tools Used At Percona

Conclusion

We looked at Sqlparse and Sqlformat tools and their use on Linux, Mac, or Windows. We would like to note that it is very convenient for developers to use the Code Editor or IDE, especially if you become a regular user. Additionally, specialized database software such as MySQL Workbench or PMM can help with query formatting. These are SQL query formatting tools that the Percona team has utilized successfully. Share your experience with these or other tools here in the blog comments.


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

编程语言实现模式

编程语言实现模式

Terence Parr / 李袁奎、尧飘海 / 华中科技大学出版社 / 2012-3-20 / 72.00元

《编程语言实现模式》旨在传授开发语言应用(工具)的经验和理念,帮助读者构建自己的语言应用。这里的语言应用并非特指用编译器或解释器实现编程语言,而是泛指任何处理、分析、翻译输入文件的程序,比如配置文件读取器、数据读取器、模型驱动的代码生成器、源码到源码的翻译器、源码分析工具、解释器,以及诸如此类的工具。为此,作者举例讲解已有语言应用的工作机制,拆解、归纳出31种易于理解且常用的设计模式(每种都包括通......一起来看看 《编程语言实现模式》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

html转js在线工具
html转js在线工具

html转js在线工具

正则表达式在线测试
正则表达式在线测试

正则表达式在线测试