How to Select NULL when the column value is default value-CodePudding

The table is set up for default value like below:

CREATE TABLE test {
    id          INT UNSIGNED NOT NULL AUTO INCREMENT,
    pname       VARCHAR(25) NOT NULL, 
    strvalue    VARCHAR(30) NOT NULL DEFAULT '____', 
    numvalue    INT NOT NULL DEFAULT 2147483647, 

    PRIMARY KEY (`id`),
    UNIQUE KEY `uidx_param` (`pname`,`strvalue`,`numvalue`)
};

I want to SELECT all the rows in the table and I want to convert default value to NULL in the result.

For example, if the following rows are saved in the table,

id  pname    strvalue  numvalue
1   'speed'  ____      20

where strvalue takes a meaningless default value.

I want to select the result as

{'id': 1, 'pname': 'speed', 'strvalue': NULL, 'numvalue': 20}

where the default value '____' for 'strvalue' column is converted into NULL(None is good also).

Somehow, I think I should use DEFAULT function in SELECT statement but I am at my wit's end.

How do I write the SELECT query to get the desired result?

CodePudding user response：

SELECT 
    CASE WHEN strvalue='_____'
    THEN null 
    ELSE strvalue END
As strvalue, column2, column3...

CodePudding user response：

Seeing as how you want strvalue to return null when not set, I don't see the point in preventing it from being null, and instead defaulting it to something else. Creating the table like this instead, would give you your desired output. You would however need to allow duplicate entries for strvalues. Otherwise you could use a simple if statement to check if 'strvalue'=="____":

CREATE TABLE test {
id          INT UNSIGNED NOT NULL AUTO INCREMENT,
pname       VARCHAR(25) NOT NULL, 
strvalue    VARCHAR(30), 
numvalue    INT NOT NULL DEFAULT 2147483647, 

PRIMARY KEY (`id`),
UNIQUE KEY `uidx_param` (`pname`,`numvalue`)

};