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
andEXECUTE
.Meanwhile,
FLOAT(m,n)
is a bad idea. It leads to two roundings. Suggest simply usingFLOAT
. Also,FLOAT
contains about 7 significant digits, yet many of your float columns seem to expect more. Perhaps you wantDOUBLE
(~16 digits) orDECIMAL
.