create table test
(
uid int(8)
)
insert into test (uid) select 1;
insert into test (uid) select 2;
insert into test (uid) select 3;
insert into test (uid) select 4;
insert into test (uid) select 5;
insert into test (uid) select 0;
insert into test (uid) select 0;
insert into test (uid) select 0;
insert into test (uid) select 0;
/*
得到这样的效果:
uid
1
2
3
4
5
0
0
0
0*//*
问的是如何通过update语句达到以下效果:
uid, 想要的uid效果
1,1
2,2
3,3
4,4
5,5
0,6
0,7
0,8
0,9
*/
(
uid int(8)
)
insert into test (uid) select 1;
insert into test (uid) select 2;
insert into test (uid) select 3;
insert into test (uid) select 4;
insert into test (uid) select 5;
insert into test (uid) select 0;
insert into test (uid) select 0;
insert into test (uid) select 0;
insert into test (uid) select 0;
/*
得到这样的效果:
uid
1
2
3
4
5
0
0
0
0*//*
问的是如何通过update语句达到以下效果:
uid, 想要的uid效果
1,1
2,2
3,3
4,4
5,5
0,6
0,7
0,8
0,9
*/
SELECT * FROM ttl WHERE uid>0
UNION
SELECT @a:=@a+1 FROM ttl WHERE uid=0
mysql> set @UUID=0;
Query OK, 0 rows affected (0.00 sec)mysql> select @UUID:=@UUID+1,UID from test3;
+----------------+------+
| @UUID:=@UUID+1 | UID |
+----------------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
+----------------+------+
9 rows in set (0.00 sec)mysql>
用用户变量实现。
select @rownum:=@rownum+1 as rn,newtable.* from newtable,(SELECT @rownum:=0) r
) temp