mysql> select * from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date | open | high | low | close | vol | adj | id | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO | 2010-07-14 | 15.32 | 15.42 | 15.2 | 15.37 | 12255700 | 15.37 | 7976402 | 1 |
| YHOO | 2010-07-13 | 15.06 | 15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 | 2 |
| YHOO | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 | 3 |
| YHOO | 2010-07-09 | 14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 | 4 |
| YHOO | 2010-07-08 | 14.43 | 14.77 | 14.4 | 14.6 | 17088700 | 14.6 | 7976406 | 5 |
| YHOO | 2010-07-07 | 14.18 | 14.42 | 14.12 | 14.4 | 17417900 | 14.4 | 7976407 | 6 |
| YHOO | 2010-07-06 | 14.23 | 14.46 | 14 | 14.13 | 17334100 | 14.13 | 7976408 | 7 |
| YHOO | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 | 8 |
| YHOO | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 | 9 |
| YHOO | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 | 10 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)mysql> delete from t2 where (date='2010-07-14');
Query OK, 1 row affected (0.00 sec)mysql> select * from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date | open | high | low | close | vol | adj | id | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO | 2010-07-13 | 15.06 | 15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 | 2 |
| YHOO | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 | 3 |
| YHOO | 2010-07-09 | 14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 | 4 |
| YHOO | 2010-07-08 | 14.43 | 14.77 | 14.4 | 14.6 | 17088700 | 14.6 | 7976406 | 5 |
| YHOO | 2010-07-07 | 14.18 | 14.42 | 14.12 | 14.4 | 17417900 | 14.4 | 7976407 | 6 |
| YHOO | 2010-07-06 | 14.23 | 14.46 | 14 | 14.13 | 17334100 | 14.13 | 7976408 | 7 |
| YHOO | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 | 8 |
| YHOO | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 | 9 |
| YHOO | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 | 10 |
| YHOO | 2010-06-29 | 14.53 | 14.54 | 13.88 | 14.04 | 31825900 | 14.04 | 7976412 | 11 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)我希望得到
mysql> select * from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date | open | high | low | close | vol | adj | id | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO | 2010-07-14 | 15.32 | 15.42 | 15.2 | 15.37 | 12255700 | 15.37 | 7976402 | 1 |
| YHOO | 2010-07-13 | 15.06 | 15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 | 2 |
| YHOO | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 | 3 |
| YHOO | 2010-07-09 | 14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 | 4 |
| YHOO | 2010-07-08 | 14.43 | 14.77 | 14.4 | 14.6 | 17088700 | 14.6 | 7976406 | 5 |
| YHOO | 2010-07-07 | 14.18 | 14.42 | 14.12 | 14.4 | 17417900 | 14.4 | 7976407 | 6 |
| YHOO | 2010-07-06 | 14.23 | 14.46 | 14 | 14.13 | 17334100 | 14.13 | 7976408 | 7 |
| YHOO | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 | 8 |
| YHOO | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 | 9 |
| YHOO | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 | 10 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)mysql> delte from t2 where (date='2010-07-14');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delte from t2 where (date='2010-07-14')' at line 1
mysql> delete from t2 where (date='2010-07-14');
Query OK, 1 row affected (0.00 sec)mysql> select * from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date | open | high | low | close | vol | adj | id | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO | 2010-07-13 | 15.06 | 15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 | 1 |
| YHOO | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 | 2 |
| YHOO | 2010-07-09 | 14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 | 3|
| YHOO | 2010-07-08 | 14.43 | 14.77 | 14.4 | 14.6 | 17088700 | 14.6 | 7976406 | 4|
| YHOO | 2010-07-07 | 14.18 | 14.42 | 14.12 | 14.4 | 17417900 | 14.4 | 7976407 | 5 |
| YHOO | 2010-07-06 | 14.23 | 14.46 | 14 | 14.13 | 17334100 | 14.13 | 7976408 | 6 |
| YHOO | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 | 7 |
| YHOO | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 | 8 |
| YHOO | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 | 9 |
| YHOO | 2010-06-29 | 14.53 | 14.54 | 13.88 | 14.04 | 31825900 | 14.04 | 7976412 | 10 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)
myid已经是主键
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date | open | high | low | close | vol | adj | id | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO | 2010-07-14 | 15.32 | 15.42 | 15.2 | 15.37 | 12255700 | 15.37 | 7976402 | 1 |
| YHOO | 2010-07-13 | 15.06 | 15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 | 2 |
| YHOO | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 | 3 |
| YHOO | 2010-07-09 | 14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 | 4 |
| YHOO | 2010-07-08 | 14.43 | 14.77 | 14.4 | 14.6 | 17088700 | 14.6 | 7976406 | 5 |
| YHOO | 2010-07-07 | 14.18 | 14.42 | 14.12 | 14.4 | 17417900 | 14.4 | 7976407 | 6 |
| YHOO | 2010-07-06 | 14.23 | 14.46 | 14 | 14.13 | 17334100 | 14.13 | 7976408 | 7 |
| YHOO | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 | 8 |
| YHOO | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 | 9 |
| YHOO | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 | 10 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)mysql> delete from t2 where (date='2010-07-14');
Query OK, 1 row affected (0.00 sec)mysql> select * from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date | open | high | low | close | vol | adj | id | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO | 2010-07-13 | 15.06 | 15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 | 2 |
| YHOO | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 | 3 |
| YHOO | 2010-07-09 | 14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 | 4 |
| YHOO | 2010-07-08 | 14.43 | 14.77 | 14.4 | 14.6 | 17088700 | 14.6 | 7976406 | 5 |
| YHOO | 2010-07-07 | 14.18 | 14.42 | 14.12 | 14.4 | 17417900 | 14.4 | 7976407 | 6 |
| YHOO | 2010-07-06 | 14.23 | 14.46 | 14 | 14.13 | 17334100 | 14.13 | 7976408 | 7 |
| YHOO | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 | 8 |
| YHOO | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 | 9 |
| YHOO | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 | 10 |
| YHOO | 2010-06-29 | 14.53 | 14.54 | 13.88 | 14.04 | 31825900 | 14.04 | 7976412 | 11 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)我希望得到
mysql> select * from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date | open | high | low | close | vol | adj | id | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO | 2010-07-14 | 15.32 | 15.42 | 15.2 | 15.37 | 12255700 | 15.37 | 7976402 | 1 |
| YHOO | 2010-07-13 | 15.06 | 15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 | 2 |
| YHOO | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 | 3 |
| YHOO | 2010-07-09 | 14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 | 4 |
| YHOO | 2010-07-08 | 14.43 | 14.77 | 14.4 | 14.6 | 17088700 | 14.6 | 7976406 | 5 |
| YHOO | 2010-07-07 | 14.18 | 14.42 | 14.12 | 14.4 | 17417900 | 14.4 | 7976407 | 6 |
| YHOO | 2010-07-06 | 14.23 | 14.46 | 14 | 14.13 | 17334100 | 14.13 | 7976408 | 7 |
| YHOO | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 | 8 |
| YHOO | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 | 9 |
| YHOO | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 | 10 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)mysql> delte from t2 where (date='2010-07-14');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delte from t2 where (date='2010-07-14')' at line 1
mysql> delete from t2 where (date='2010-07-14');
Query OK, 1 row affected (0.00 sec)mysql> select * from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date | open | high | low | close | vol | adj | id | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO | 2010-07-13 | 15.06 | 15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 | 1 |
| YHOO | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 | 2 |
| YHOO | 2010-07-09 | 14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 | 3|
| YHOO | 2010-07-08 | 14.43 | 14.77 | 14.4 | 14.6 | 17088700 | 14.6 | 7976406 | 4|
| YHOO | 2010-07-07 | 14.18 | 14.42 | 14.12 | 14.4 | 17417900 | 14.4 | 7976407 | 5 |
| YHOO | 2010-07-06 | 14.23 | 14.46 | 14 | 14.13 | 17334100 | 14.13 | 7976408 | 6 |
| YHOO | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 | 7 |
| YHOO | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 | 8 |
| YHOO | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 | 9 |
| YHOO | 2010-06-29 | 14.53 | 14.54 | 13.88 | 14.04 | 31825900 | 14.04 | 7976412 | 10 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)
myid已经是主键
解决方案 »
- mysql_real_escape_string不能处理的字符串
- 在mysql里测试了一下发现加引号要快于不加引号.
- 请教 PostgreSQL 创建收支平衡 视图 的方法
- 关于mysql的sleep进程的用处
- 比如现在要显示6月份按周统计的报表,如何按周显示,用的是mysql。哪位兄弟指点下~~
- 如何将mysql的表结构导出到access
- 关于MY SQL的一些语法
- vs2010与mysql数据库如何建立连接 进行数据交互
- 有没有MySQL Workbench 6.0 CE的好的中文教程?
- mysql 日期格式运算
- 如何让80万数据的表select count更快?
- 源码安装mysql5.1发现不支持innodb ,如何配置才能让他支持
那就不要用 自增。可以考虑触发器试试
MySQL中的ROWNUM的实现
drop table if exists ttt;
CREATE TABLE `ttt` (
`name` varchar(64) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ttt` (`name`,`id`) VALUES ('a',1);
INSERT INTO `ttt` (`name`,`id`) VALUES ('b',2);
INSERT INTO `ttt` (`name`,`id`) VALUES ('c',3);
INSERT INTO `ttt` (`name`,`id`) VALUES ('d',4);
INSERT INTO `ttt` (`name`,`id`) VALUES ('e',5);
INSERT INTO `ttt` (`name`,`id`) VALUES ('f',6);drop table if exists ttt_tmp;
CREATE TABLE `ttt_tmp` (
`name` varchar(64) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 上面是测试数据
mysql> select * from ttt;
+------+----+
| name | id |
+------+----+
| a | 1 |
| b | 2 |
| c | 3 |
| d | 4 |
| e | 5 |
| f | 6 |
+------+----+
6 rows in set (0.01 sec)mysql> truncate ttt_tmp;
Query OK, 0 rows affected (0.06 sec)mysql> delete from ttt where name='a';
Query OK, 1 row affected (0.02 sec)mysql> insert into ttt_tmp(name) select name from ttt order by id;
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql> rename table ttt_tmp to ttt_tmp1,ttt to ttt_tmp,ttt_tmp1 to ttt;
Query OK, 0 rows affected (0.11 sec)mysql> select * from ttt;
+------+----+
| name | id |
+------+----+
| b | 1 |
| c | 2 |
| d | 3 |
| e | 4 |
| f | 5 |
+------+----+
5 rows in set (0.00 sec)
+------+----+
| name | id |
+------+----+
| a | 1 |
| b | 2 |
| c | 3 |
| d | 4 |
| e | 5 |
| f | 6 |
+------+----+
6 rows in set (0.00 sec)mysql> delete from ttt where name='a';
Query OK, 1 row affected (0.03 sec)mysql> select * from ttt;
+------+----+
| name | id |
+------+----+
| b | 2 |
| c | 3 |
| d | 4 |
| e | 5 |
| f | 6 |
+------+----+
5 rows in set (0.00 sec)mysql> set @rownum:=0;
Query OK, 0 rows affected (0.00 sec)mysql> update ttt,(select id,@rownum:=@rownum+1 as rownum from ttt) as t2 set ttt.id=t2.rownum where ttt.id=t2.id;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0mysql> select * from ttt;
+------+----+
| name | id |
+------+----+
| b | 1 |
| c | 2 |
| d | 3 |
| e | 4 |
| f | 5 |
+------+----+
5 rows in set (0.00 sec)
update ttt,(select id,@rownum:=@rownum+1 as rownum from ttt order by id) as t2 set ttt.id=t2.rownum where ttt.id=t2.id;