MySQL – How to Get Column Name Based on Value in Table

mariadbMySQL

I have the given table:

mysql> describe streaming_innodb; 
+---------------+------------------------+------+-----+---------------------+-----------------------------+
| Field         | Type                   | Null | Key | Default             | Extra                       |
+---------------+------------------------+------+-----+---------------------+-----------------------------+
| row_id        | bigint(20) unsigned    | NO   | PRI | NULL                | auto_increment              |
| create_time   | timestamp              | NO   | MUL | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| user_time     | timestamp              | NO   | MUL | 0000-00-00 00:00:00 |                             |
| uTINYINT      | tinyint(3) unsigned    | YES  |     | NULL                |                             |
| uSMALLINT     | smallint(5) unsigned   | YES  |     | NULL                |                             |
| uMEDIUMINT    | mediumint(8) unsigned  | YES  |     | NULL                |                             |
| uINTEGER      | int(10) unsigned       | YES  |     | NULL                |                             |
| uBIGINT       | bigint(20) unsigned    | YES  |     | NULL                |                             |
| uDecimal_10_5 | decimal(10,5) unsigned | YES  |     | NULL                |                             |
| uDecimal_10_0 | decimal(10,0) unsigned | YES  |     | NULL                |                             |
| uFloat_30_15  | float(30,15) unsigned  | YES  |     | NULL                |                             |
| uFloat_10_10  | float(10,10) unsigned  | YES  |     | NULL                |                             |
| sTINYINT      | tinyint(4)             | YES  |     | NULL                |                             |
| sSMALLINT     | smallint(6)            | YES  |     | NULL                |                             |
| sMEDIUMINT    | mediumint(9)           | YES  |     | NULL                |                             |
| sINTEGER      | int(11)                | YES  |     | NULL                |                             |
| sBIGINT       | bigint(20)             | YES  |     | NULL                |                             |
| sDecimal_4_1  | decimal(4,1)           | YES  |     | NULL                |                             |
| sDecimal_10_2 | decimal(10,2)          | YES  |     | NULL                |                             |
| sFloat_2_0    | float(2,0)             | YES  |     | NULL                |                             |
| sFloat_15_8   | float(15,8)            | YES  |     | NULL                |                             |
| rCHAR5        | char(5)                | YES  |     | NULL                |                             |
| rCHAR100      | varchar(100)           | YES  |     | NULL                |                             |
| rDATE         | datetime               | YES  |     | NULL                |                             |
| rTIME         | time                   | YES  |     | NULL                |                             |
| rDATETIME     | datetime               | YES  |     | NULL                |                             |
| rTIMESTAMP    | timestamp              | NO   |     | 0000-00-00 00:00:00 |                             |
+---------------+------------------------+------+-----+---------------------+-----------------------------+
27 rows in set (0.00 sec)

Assuming I want to find the value of a column, based on a value in another column, I can use the LIKE method

Example:

mysql> select user_time from streaming_scaledb where rTime like "04:44:21";
+---------------------+
| user_time           |
+---------------------+
| 2019-03-18 04:52:05 |
+---------------------+
1 row in set (0.02 sec)

However, for this problem I only know the value "04:44:21"; ie I need to find X and don't know the expected user_time.

mysql> select user_time from streaming_scaledb where X like "04:44:21";

Is there any query I can use such in order to find X?

Best Answer

Plan A: Client code to construct the query with the variable column name.

Plan B: Do a similar thing, but using a Stored Routine in which you use CONCAT, PREPARE and EXECUTE.

Meanwhile, FLOAT(m,n) is a bad idea. It leads to two roundings. Suggest simply using FLOAT. Also, FLOAT contains about 7 significant digits, yet many of your float columns seem to expect more. Perhaps you want DOUBLE (~16 digits) or DECIMAL.