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

How to choose all the columns except one column in MySQL, I believe many inexperienced people are at a loss about this. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

SQL makes it very easy to select all the fields in the table through the SELECT * (SELECT ALL) clause. Unfortunately, once you remove a column from the list, the SELECT ALL statement disappears. Writing the name of each column can quickly become tedious, especially if you happen to be dealing with a table that contains many columns. What if we could choose each column except one column-by excluding rather than including? It can be done. In fact, there are two ways to do this-one is simple, the other is slightly simpler.

Two methods are described in detail below:

Method 1: use the INFORMATION_ schema. Columns table

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server, such as the name of the database or table, the data type of the column, or access rights. More specifically, the COLUMNS table provides information about the columns in the table, including column names.

The film table of the Sakila sample database contains up to 13 columns.

This is how we use the INFORMATION_SCHEMA.COLUMNS table to get all the columns except the original_language_id column:

The GROUP_CONCAT function concatenates all column names into a comma-separated string. Then we can replace the field with an empty string!

Execute query

A small obstacle to overcome is that MySQL queries cannot accept dynamic column names. The solution is to adopt a prepared declaration. Here is the code to set the @ sql variable, prepare the statement, and execute it:

Inserting column, table, and schema information into the query produces the desired results:

Method 2: use Navicat

The main goal of database development and management tools such as Navicat is to improve productivity. Therefore, Navicat is designed to make your work as fast and easy as possible. To do this, the SQL editor helps you write code faster with Code completion and customizable code snippets that advise keywords and eliminate duplicate code. If that's not enough, Navicat also provides a useful tool called Query Builder to build queries visually. It allows you to create and edit queries with only a little knowledge of SQL. Although Query Builder is mainly sold to more novice programmers, people who are proficient in SQL can still accomplish some tasks from Query Builder. One of such tasks is to select columns.

In query Builder, there is a check box next to the table name to select all its columns. If you click it, you can simply uncheck the original_language_id field to remove it from the column list:

Click the OK button, then close the dialog box and add the SQL code to the editor:

Compared to writing code by hand, using query builder to create a query has several advantages:

It minimizes input errors

It generates an easy-to-read formatted SQL

After reading the above, do you know how to select all but one column in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.