Moze li se sql-om napuniti polje tabele vrednostima x,x+1,x+2,x+3.... i sve tako dok ima slogova ?

[ bogdan.kecman @ 05.07.2014. 18:14 ] @

moze, problem je samo to sto ne mozes da utices na red, mozes da dobijes kao ovde u primeru:1,2,3,4...10.. a moze ladno da ti se desi da bude 1,2,4,5,7,8,3,6,10,9 .. posto ti mysql (niti sql standard) ne garantuju kojim redom ce se izvrsavati taj update ako nemas order by ..

Code:

mysql> create table t1 (f1 int auto_increment not null primary key, f2 int not null default 0) engine=myisam;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 (f2) values (rand()*1000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 (f2) values (rand()*1000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (f2) values (rand()*1000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (f2) select rand()*1000 from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

...

mysql> insert into t1 (f2) select rand()*1000 from t1;
Query OK, 6144 rows affected (0.01 sec)
Records: 6144  Duplicates: 0  Warnings: 0

mysql> select * from t1 limit 10;
+----+-----+
| f1 | f2  |
+----+-----+
|  1 | 144 |
|  2 | 787 |
|  3 | 505 |
|  4 | 163 |
|  5 | 300 |
|  6 |  12 |
|  7 | 158 |
|  8 | 757 |
|  9 | 309 |
| 10 | 274 |
+----+-----+
10 rows in set (0.00 sec)

mysql> set @brojac:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set f2=(@brojac:=@brojac+1);
Query OK, 12288 rows affected (0.02 sec)
Rows matched: 12288  Changed: 12288  Warnings: 0

mysql> select * from t1 limit 10;
+----+----+
| f1 | f2 |
+----+----+
|  1 |  1 |
|  2 |  2 |
|  3 |  3 |
|  4 |  4 |
|  5 |  5 |
|  6 |  6 |
|  7 |  7 |
|  8 |  8 |
|  9 |  9 |
| 10 | 10 |
+----+----+
10 rows in set (0.00 sec)

e sad, mysql ti dozvoljava da ovde imas order by, dakle:

Code:

mysql> set @brojac:=0; update t1 set f2=(@brojac:=@brojac+1) order by f1 desc;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)
Rows matched: 12288  Changed: 0  Warnings: 0

mysql> select * from t1 limit 10;
+----+-------+
| f1 | f2    |
+----+-------+
|  1 | 12288 |
|  2 | 12287 |
|  3 | 12286 |
|  4 | 12285 |
|  5 | 12284 |
|  6 | 12283 |
|  7 | 12282 |
|  8 | 12281 |
|  9 | 12280 |
| 10 | 12279 |
+----+-------+
10 rows in set (0.00 sec)

mysql> select * from t1 order by f1 desc limit 10;
+-------+----+
| f1    | f2 |
+-------+----+
| 12288 |  1 |
| 12287 |  2 |
| 12286 |  3 |
| 12285 |  4 |
| 12284 |  5 |
| 12283 |  6 |
| 12282 |  7 |
| 12281 |  8 |
| 12280 |  9 |
| 12279 | 10 |
+-------+----+
10 rows in set (0.00 sec)

mysql>

samo sto koliko ja znam ovo nije po standardu (dakle ovaj order by ovde nije po sql standardu vec je mysql extenzija), nemoj da me drzis za rec mozda gresim