mysql替换查询和模糊查询

mysql> select * from alert_config limit 1;    

                                                                                     

+----+---------------+-------------+-------------+------------+------+--------+---------------------+
| id | ip            | description | explanation | upperLimit | type | status | createTime          |
+----+---------------+-------------+-------------+------------+------+--------+---------------------+
|  1 | 192.168.0.126 | | NULL        |         90 |    1 |      0 | 2009-06-24 12:00:53 |
+----+---------------+-------------+-------------+------------+------+--------+---------------------+
1 row in set (0.00 sec)

 

mysql> select * from alert_mobile_history_log limit 1; 

                                                                            

+----+---------+-------+---------------------+
| id | alertId | value | createTime          |
+----+---------+-------+---------------------+
|  1 |       1 | 0     | 2009-06-24 11:55:03 |
+----+---------+-------+---------------------+
1 row in set (0.00 sec)

 

mysql> select a.ip,b.alertId,a.description,b.value,b.createTime from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId limit 1;

                                                                                        
+---------------+---------+-------------+-------+---------------------+
| ip            | alertId | description | value | createTime          |
+---------------+---------+-------------+-------+---------------------+
| 192.168.0.126 |       1 | | 0     | 2009-06-24 11:55:03 |
+---------------+---------+-------------+-------+---------------------+
1 row in set (0.00 sec)

 

要把description 字段下的@value@替换为value字段下的值。

 

mysql> select a.ip,b.alertId,replace(a.description,'@value@',b.value) as value2,b.createTime from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId limit 1;

                                                         
+---------------+---------+--------+---------------------+
| ip            | alertId | value2 | createTime          |
+---------------+---------+--------+---------------------+
| 192.168.0.126 |       1 | cpu0%  | 2009-06-24 11:55:03 |
+---------------+---------+--------+---------------------+
1 row in set (0.00 sec)

 

查询value2字段下有load的数据。

 

mysql> select a.ip,b.alertId,replace(a.description,'@value@',b.value) as value2,b.createTime from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId and value2 like 'load%' limit 1;  
ERROR 1054 (42S22): Unknown column 'value2' in 'where clause'

 

value2不能做为条件使用。

 

mysql> select a.ip,b.alertId,replace(a.description,'@value@',b.value) as value2,b.createTime from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId and a.description like 'load%' limit 1;

+---------------+---------+-----------+---------------------+
| ip            | alertId | value2    | createTime          |
+---------------+---------+-----------+---------------------+
| 192.168.0.126 |       3 | load 0.00 | 2009-06-24 15:40:02 |
+---------------+---------+-----------+---------------------+
1 row in set (0.00 sec)

 

ok！