Return proper column based on other column's values Consider an EAV table with separate columns for each datatype:

CREATE TABLE eav_values (
    id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entity_id INT NOT NULL,
    name VARCHAR(127) NOT NULL,
    type SET('varchar','int','datetime') NOT NULL,
    col_varchar VARCHAR(1023) NULL DEFAULT NULL,
    col_int INT NULL DEFAULT NULL,
    col_datetime DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB;


Now I'd like to store the username column for entity_id=5, which is a VARCHAR:

INSERT INTO eav_values (entity_id, name, type, col_varchar)
    VALUES (5, 'username', 'varchar', 'jeffatwood');


How would one construct the SELECT statement to select from the proper column with a single query?

SELECT (MAGIC HERE) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;


Might I be best off simply COALESCEing the values and carefully managing the data in application code? Is there no real performance penalty to such a SELECT?

SELECT COALESCE(col_varchar, col_int, col_datetime) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;


Or perhaps duplicate the data into a redundant read VARCHAR column for reading?

INSERT INTO eav_values (entity_id, name, type, col_varchar, read)
    VALUES (5, 'username', 'varchar', 'jeffatwood', 'jeffatwood');

SELECT read FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;


Note that I would like to use different datatypes for each value as in some cases I might need to index or perform datype-specific logic on certain columns, in those cases I'll know which datatype or I'll use a separate SELECT to get the column type.
CASE type 
WHEN 'varchar'  THEN col_varchar 
WHEN 'int'      THEN col_int 
WHEN 'datetime' THEN col_datetime 
END