SELECT everything (but one column as null)

Question

in mysql, is it possible to get all columns and data from a table, but select column ID as NULL?

Something like

SELECT *, id AS (SELECT '') FROM my_table WHERE 1 

I want to get an identifier column, so it did not spoil the number of columns for later data loading, but I want to exclude the column value so that it does not cause key conflicts.

Thanks!

Answer 1

Thus, it is not possible to use SELECT * and replace one of the columns with another value.

If you do not want to use the select statement specified in the application code, you can create a view with the fields filled in and the identifier will be deleted. You still have to specify all columns at least once.

 select NULL as ID, t.col2, t.col3, t.col4 from mytable t where col2 = 1 

Here is an easy way to get all column names:

 SELECT column_name FROM information_schema.columns WHERE table_name = mytable 

Answer 2

 SELECT NULL AS ID, Column1, Column2, ..., ColumnN FROM my_table 

Answer 3

What about

 SELECT *, NULL AS id FROM my_table 

You would get id twice, so you need to expand * to all column names, for each table in which you want it to run.

(If you want the column names to be extracted automatically, you can probably use vendor-specific functions, I can’t remember if MySQL has one for this situation).

Answer 4

Since it sounds like it's always a preparation for a data dump, use a temporary table

 CREATE TEMP TABLE t000 AS SELECT * FROM my_table; -- or INSERT INTO UPDATE t000 SET id = NULL; 

Answer 5

You cannot do this. This is not allowed in SQL. You must write all the fields and then specify null for id.

SELECT a,b,c,d,e, id as NULL from table

Answer 6

You can do something like:

 SELECT null AS id, t.* FROM my_table t 

But it will contain the identifier twice. You will need to specify columns if you only need a column with a null identifier.

 SELECT null As id, t.col1, t.col2 FROM my_table t 

Answer 7

A truly relational language Tutorial D allows you to express a projection (all other other relevant relational operators) in terms of attributes to be deleted instead of those that need to be stored, for example

 my_relvar { ALL BUT ID } 

(but being really relational Tutorial D, of course, has no null concept!)

Unfortunately, SQL does not have such a language function, and you must write with long names for all columns.