CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Renato Rodrigues
Renato Rodrigues

Reputation: 1038

SELECT DEFAULT returns NULL

I want to return two defaults column values even if the table has no records. I'm using the following query (thanks to How to SELECT DEFAULT value of a field):

SELECT DEFAULT(membership_credits) AS membership_credits,
       DEFAULT(product_credits) AS product_credits
FROM (SELECT 1) AS dummy LEFT JOIN Users ON True LIMIT 1

But instead of the default values, I'm getting NULL:

membership_credits  product_credits
              NULL             NULL

What's the problem?

EDIT:

Adding the table schema as suggested in a comment:

CREATE TABLE Users (
  user_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_login VARCHAR(40) NOT NULL UNIQUE,
  user_name VARCHAR(100) NOT NULL,
  user_email VARCHAR(254) NOT NULL UNIQUE,
  user_telephone VARCHAR(100) NOT NULL,
  user_password VARCHAR(64) NOT NULL,
  user_address VARCHAR(255) NOT NULL,
  user_postal_code VARCHAR(100) NOT NULL,
  user_district VARCHAR(100) NOT NULL,
  user_country VARCHAR(100) NOT NULL,
  user_tax_number VARCHAR(20) NOT NULL,
  user_billing_email VARCHAR(254) NOT NULL,
  company_description TEXT,
  company_history TEXT,
  company_products TEXT,
  public_contact BINARY(1) NOT NULL,
  user_active BINARY(1) NOT NULL DEFAULT '0',
  user_key VARCHAR(255) NOT NULL,
  user_registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  unread_messages INT UNSIGNED DEFAULT 0,
  membership_credits INT UNSIGNED NOT NULL DEFAULT 0,
  product_credits INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;

Upvotes: 0

Views: 215

Answers (5)

wchiquito
wchiquito

Reputation: 16551

It all depends on if your columns allow NULL or not.

If NULL allow, a query like the following will be useful, so the table does not have records:

SELECT
  DEFAULT(`membership_credits`) `membership_credits`,
  DEFAULT(`product_credits`) `product_credits`
FROM (SELECT 1) `dummy`
  LEFT JOIN `users` ON TRUE
LIMIT 1;

SQL Fiddle demo

If not allow NULL, and the table has no record, you will get a NULL to the query above. In this case would require a query like:

SELECT
  DEFAULT(`membership_credits`) `membership_credits`,
  DEFAULT(`product_credits`) `product_credits`
FROM (SELECT *, COUNT(0)
    FROM `users`) `users`;

SQL Fiddle demo

UPDATE

Be careful in MySQL >= 5.6 does not operate in the same way and NULL values ​​are obtained.

SQL Fiddle demo

Upvotes: 1

kartavya
kartavya

Reputation: 342

Use below query :

SELECT IF(COUNT(user_id) = 0, DEFAULT( membership_credits), membership_credits) AS membership_credits, IF(COUNT(user_id) = 0, DEFAULT( product_credits), product_credits) AS product_credits FROM (SELECT 1) AS dummy LEFT JOIN Users ON TRUE

Upvotes: 0

kartavya
kartavya

Reputation: 342

Use the below query :

SELECT IF(COUNT(user_id) = 0, DEFAULT( membership_credits), membership_credits) AS membership_credits, IF(COUNT(user_id) = 0, DEFAULT( product_credits), product_credits) AS product_credits FROM (SELECT 1) AS dummy LEFT JOIN Users ON TRUE

Upvotes: -1

Fathah Rehman P
Fathah Rehman P

Reputation: 8741

if you use following query to create table 

CREATE TABLE `users` (
    `user_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_login` VARCHAR(40) NOT NULL,
    `user_name` VARCHAR(100) NOT NULL,
    `user_email` VARCHAR(254) NOT NULL,
    `user_telephone` VARCHAR(100) NOT NULL,
    `user_password` VARCHAR(64) NOT NULL,
    `user_address` VARCHAR(255) NOT NULL,
    `user_postal_code` VARCHAR(100) NOT NULL,
    `user_district` VARCHAR(100) NOT NULL,
    `user_country` VARCHAR(100) NOT NULL,
    `user_tax_number` VARCHAR(20) NOT NULL,
    `user_billing_email` VARCHAR(254) NOT NULL,
    `company_description` TEXT NULL,
    `company_history` TEXT NULL,
    `company_products` TEXT NULL,
    `public_contact` BINARY(1) NOT NULL,
    `user_active` BINARY(1) NOT NULL DEFAULT '0',
    `user_key` VARCHAR(255) NOT NULL,
    `user_registered` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `unread_messages` INT(10) UNSIGNED NULL DEFAULT '0',
    `membership_credits` INT(10) UNSIGNED NULL DEFAULT '0',
    `product_credits` INT(10) UNSIGNED NULL DEFAULT '0',
    PRIMARY KEY (`user_id`),
    UNIQUE INDEX `user_login` (`user_login`),
    UNIQUE INDEX `user_email` (`user_email`)
)

And use below query to get default values

            SELECT if(DEFAULT(membership_credits) is null,0,
DEFAULT(membership_credits))AS membership_credits,if(DEFAULT(product_credits)
 is null,0,DEFAULT(product_credits) )AS product_credits FROM
 (SELECT 1) AS dummy LEFT JOIN Users ON True LIMIT 1

You must note that in the above create query membership_credits and product_credits modified to allow null

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 4591

Try this Query 

SELECT COLUMN_DEFAULT FROM information_schema.columns
WHERE TABLE_SCHEMA = 'your_database_name' AND 
TABLE_NAME = 'your_table_name' AND COLUMN_NAME = 'your_column_name';

In Your case 

SELECT COLUMN_DEFAULT FROM information_schema.columns
WHERE TABLE_SCHEMA = 'test' AND
TABLE_NAME = 'users' AND 
COLUMN_NAME in ('membership_credits','product_credits');

Upvotes: 1

Related Questions