SELECT all（但是一列为null）

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

在mysql中,是否可以从表中获取所有列和数据,但是将ID列选为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 doesn't cause key conflicts.

我想拉入ID列,因此它不会弄乱后续数据加载的列数,但我想消除列的值,因此它不会导致密钥冲突。

Thanks!

7 个解决方案

#1

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

因此,无法使用SELECT *并将其中一个列替换为另一个值。

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语句,则可以创建一个视图,其中填充了这些字段并且ID已清空。您仍需要至少为所有列命名一次。

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

#2

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

#3

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.

您将ID列出两次,因此您需要将*扩展为所有列名,对于要在其上运行的每个表。

(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).

(如果您希望自动提取列名,您可以使用特定于供应商的功能;我不记得MySQL是否有这种情况)。

#4

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;

#5

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

你不能这样做。这在SQL中是不允许的。你应该写所有字段然后为id说null。

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

从表中选择a,b,c,d,e,id为NULL

#6

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.

但它会包含两次id。如果只需要null id列,则需要指定列。

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

#7

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.

真正的关系语言教程D允许投影(所有其他相关的关系运算符)用要删除的属性而不是要保留的属性表示。

my_relvar { ALL BUT ID }

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

(但真正的关系教程D当然没有null的概念!)

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

遗憾的是,SQL没有这样的语言功能,你必须用手写所有的列名。

#1