链接： http://blog.itpub.net/28602568/viewspace-2092790/

变量：

mysql> select @host:=host from mysql.user where host!='localhost';
+----------------+
| @host:=host |
+----------------+
| 127.0.0.1 | --> set @host:='127.0.0.1';
| ::1 | --> set @host:='::1';

| wonhigh-test16 | --> set @host:='wonhigh-test16';

+----------------+
mysql> select @host; ---查询时只会取最后一行....不会是自己想的3行都显示；
+---------------+
| @host |
+----------------+
| wonhigh-test16 |
+----------------+
mysql>

rownum：

mysql> select host,@rownum:=@rownum+1 AS rownum from mysql.user,(SELECT @rownum:=0) r where host!='localhost';
+-----------------+--------------+
| host | rownum |
+-----------------+--------------+

| 127.0.0.1 | 1 |

| ::1 | 2 |

| wonhigh-test16 | 3 |

+------------------+------------+

mysql>

或者

set @rownum:=0;
select host,@rownum:=@rownum+1 AS rownum from mysql.user where host!='localhost';

一行拆多行：

mysql>select @host:=GROUP_CONCAT(host) host1 from mysql.user where host!='localhost';
+---------------------------------+
| host1 |
+--------------------------------+
| 127.0.0.1,::1,wonhigh-test16 |
+---------------------------------+

mysql>

mysql> SELECT substring_index(substring_index(a.host1, ',', b.id),',' ,-1 ) as host1_,b.id
FROM (select @host:=GROUP_CONCAT(host) host1 from mysql.user where host!='localhost') a
JOIN (select 1 id union all select 2 union all select 3) b

ON b.id<= (length(a.host1) - length(REPLACE(a.host1, ',', '')) + 1) order by b.id;

+-------------------+---------------+

| host1_ | id |

+------------------+-----------------+

| 127.0.0.1 | 1 |

| ::1 | 2 |

| wonhigh-test16 | 3 |

+-----------------+----------------+

mysql>

【源于本人笔记】若有书写错误，表达错误，请指正...

此条目发表在 MySQL分类目录。将固定连接加入收藏夹。

MySQL 变量 、rownum 、 一行拆多行..小知识点

MySQL 变量、rownum 、一行拆多行..小知识点