Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief description of the two detailed methods for selecting all the columns except one column in MySQL. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, let's go straight to the topic, and hope to choose two detailed methods for all columns except one column in MySQL. This article can bring you some practical help.

Two methods are described in detail below:

Method 1: use the INFORMATION_ schema. Columns table

INFORMATION_SCHEMA provides access to database metadata and information about MySQL cloud servers, 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

Two detailed methods for selecting all but one column in the MySQL will first tell you about it. If you want to know about other related issues, you can continue to follow our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.