0

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?

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.