SELECT all (but one column as null)

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

Something like

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

I want to pull in the ID column, so it doesn't mess up the column count for a later data load, but I want to eliminate the column's value so it开发者_如何学Go doesn't cause key conflicts.

Thanks!

So, there is no way to use SELECT * and replace one of the columns with another value.

If you don't want to use the select statement provided in your application code, you can create a view with those fields populated and the ID nulled out. You'll still have to name all the columns at least once.

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

Here is the easy way to get all of your column names:

SELECT column_name FROM information_schema.columns WHERE table_name = mytable

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

What about

SELECT *, NULL AS id FROM my_table

You'd get id listed twice, so you need to expand the * to all the column names, for each table you want this to run on.

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

Since it sounds like this is preparatory to a data dump anyway, use a temp table

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

You can't do that. This is not allowed in SQL. You should write all fields and then say null for id.

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

You can do something like:

SELECT null AS id, t.* FROM my_table t

But it will include the id twice. You'll need to specify the columns if you only want the null id column.

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

The truly relational language Tutorial D allows a projection (all other other relevant relational operators) to be expressed in terms of the attributes to be removed instead of the ones to be kept e.g.

my_relvar { ALL BUT ID }

(but being truly relational Tutorial D of course doesn't have a concept of null!)

Sadly, SQL does not have such a language feature and you must write in longhand all the column names.