Get column name based on value in table

Question

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?

Answer 1

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.

• Rick James: I got so fed up with saying criticizing (m,n), that I filed this bug report.