2)的sql文以及结果
mysql> select * from ALLCOUNT where DateTime <= '2003/08/11' order by DateTime limit 10;
+------------+--------------+-------------+--------------+-------------+
| DateTime | AllKwdCookie | AllKwdCount | AllURLCookie | AllURLCount |
+------------+--------------+-------------+--------------+-------------+
| 2003/08/03 | 179523 | 20225 | 166938 | 64160 |
| 2003/08/04 | 178936 | 20631 | 165948 | 63174 |
| 2003/08/05 | 181255 | 20499 | 171849 | 65116 |
| 2003/08/06 | 175610 | 20002 | 166093 | 63465 |
| 2003/08/07 | 188486 | 20761 | 185929 | 67592 |
| 2003/08/08 | 210871 | 21210 | 219330 | 74330 |
| 2003/08/09 | 224249 | 22236 | 234098 | 81990 |
| 2003/08/10 | 167965 | 18464 | 166230 | 63896 |
| 2003/08/11 | 179773 | 20250 | 177891 | 68178 |
+------------+--------------+-------------+--------------+-------------+
9 rows in set (0.00 sec)mysql> select * from ALLCOUNT where DateTime >= '2003/08/10' order by DateTime limit 10;
+------------+--------------+-------------+--------------+-------------+
| DateTime | AllKwdCookie | AllKwdCount | AllURLCookie | AllURLCount |
+------------+--------------+-------------+--------------+-------------+
| 2003/08/10 | 167965 | 18464 | 166230 | 63896 |
| 2003/08/11 | 179773 | 20250 | 177891 | 68178 |
| 2003/08/12 | 193568 | 20814 | 198085 | 69889 |
| 2003/08/13 | 181166 | 18898 | 182024 | 63891 |
| 2003/08/14 | 194177 | 20425 | 192266 | 68594 |
| 2003/08/15 | 185653 | 19190 | 184531 | 64928 |
| 2003/08/16 | 171902 | 18741 | 168505 | 62526 |
| 2003/08/17 | 186875 | 20345 | 181539 | 67322 |
| 2003/08/18 | 179347 | 20303 | 171451 | 63656 |
| 2003/08/19 | 162491 | 16660 | 177173 | 67574 |
+------------+--------------+-------------+--------------+-------------+
10 rows in set (0.00 sec)mysql> select * from ALLCOUNT where DateTime <= '2003/08/11' and DateTime >= '2003/08/10' order by DateTime limit 10
;
Empty set (0.00 sec)mysql>
mysql> select * from ALLCOUNT where DateTime <= '2003/08/11' order by DateTime limit 10;
+------------+--------------+-------------+--------------+-------------+
| DateTime | AllKwdCookie | AllKwdCount | AllURLCookie | AllURLCount |
+------------+--------------+-------------+--------------+-------------+
| 2003/08/03 | 179523 | 20225 | 166938 | 64160 |
| 2003/08/04 | 178936 | 20631 | 165948 | 63174 |
| 2003/08/05 | 181255 | 20499 | 171849 | 65116 |
| 2003/08/06 | 175610 | 20002 | 166093 | 63465 |
| 2003/08/07 | 188486 | 20761 | 185929 | 67592 |
| 2003/08/08 | 210871 | 21210 | 219330 | 74330 |
| 2003/08/09 | 224249 | 22236 | 234098 | 81990 |
| 2003/08/10 | 167965 | 18464 | 166230 | 63896 |
| 2003/08/11 | 179773 | 20250 | 177891 | 68178 |
+------------+--------------+-------------+--------------+-------------+
9 rows in set (0.00 sec)mysql> select * from ALLCOUNT where DateTime >= '2003/08/10' order by DateTime limit 10;
+------------+--------------+-------------+--------------+-------------+
| DateTime | AllKwdCookie | AllKwdCount | AllURLCookie | AllURLCount |
+------------+--------------+-------------+--------------+-------------+
| 2003/08/10 | 167965 | 18464 | 166230 | 63896 |
| 2003/08/11 | 179773 | 20250 | 177891 | 68178 |
| 2003/08/12 | 193568 | 20814 | 198085 | 69889 |
| 2003/08/13 | 181166 | 18898 | 182024 | 63891 |
| 2003/08/14 | 194177 | 20425 | 192266 | 68594 |
| 2003/08/15 | 185653 | 19190 | 184531 | 64928 |
| 2003/08/16 | 171902 | 18741 | 168505 | 62526 |
| 2003/08/17 | 186875 | 20345 | 181539 | 67322 |
| 2003/08/18 | 179347 | 20303 | 171451 | 63656 |
| 2003/08/19 | 162491 | 16660 | 177173 | 67574 |
+------------+--------------+-------------+--------------+-------------+
10 rows in set (0.00 sec)mysql> select * from ALLCOUNT where DateTime <= '2003/08/11' and DateTime >= '2003/08/10' order by DateTime limit 10
;
Empty set (0.00 sec)mysql>
解决方案 »
- centOS6.8 安装Mysql5.5后,无法启动server,启动木有产生错误日志
- 关于innodb文件结构以及聚集索引的问题
- 请教大家,mysql查询未使用主键的方法!
- mysql 自定义函数中 select 语句赋值
- 哪有PostgreSQL odbc驱动下载
- 复合索引的问题,建立复合索引对字段数有什么限制吗?
- mysql 有没有批执行一个sql文件的参数?
- mssql调试正常,mysql为什么报错!!!
- 为什么我在导入sql脚本的时候出现一长串的帮助信息
- 在MYSQL中我想修改root密码,可是总报错说user表是read only,如何把只读的属性去掉?
- 在线等,key的一句SQL语句的解释
- 求助MYSQL ROOT帐户的权限被修改 不能进行管理 请问何如来恢复 或者换个帐户
mysql> desc ALLCOUNT;
+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| DateTime | varchar(6) | | PRI | | |
| AllKwdCookie | int(10) unsigned | | | 0 | |
| AllKwdCount | int(10) unsigned | | | 0 | |
| AllURLCookie | int(10) unsigned | | | 0 | |
| AllURLCount | int(10) unsigned | | | 0 | |
+--------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)mysql> select * from ALLCOUNT limit 10;
+------------+--------------+-------------+--------------+-------------+
| DateTime | AllKwdCookie | AllKwdCount | AllURLCookie | AllURLCount |
+------------+--------------+-------------+--------------+-------------+
| 2003/08/03 | 179523 | 20225 | 166938 | 64160 |
| 2004/01/01 | 135792 | 11208 | 170658 | 60828 |
| 2004/01/02 | 141121 | 12399 | 179837 | 68059 |
| 2004/01/03 | 147457 | 13218 | 183562 | 72012 |
| 2004/01/04 | 166046 | 14771 | 209884 | 83963 |
| 2004/01/05 | 167914 | 15156 | 213344 | 85690 |
| 2004/01/06 | 163873 | 15318 | 208529 | 84720 |
| 2004/01/07 | 161460 | 15369 | 206321 | 85636 |
| 2004/01/08 | 151148 | 14689 | 189264 | 79830 |
| 2004/01/09 | 138990 | 13723 | 172255 | 71874 |
+------------+--------------+-------------+--------------+-------------+
10 rows in set (0.00 sec)mysql> select * from ALLCOUNT where DateTime = '2003/08/03';
Empty set (0.00 sec)mysql> select * from ALLCOUNT where DateTime like '2003/08/03';
+------------+--------------+-------------+--------------+-------------+
| DateTime | AllKwdCookie | AllKwdCount | AllURLCookie | AllURLCount |
+------------+--------------+-------------+--------------+-------------+
| 2003/08/03 | 179523 | 20225 | 166938 | 64160 |
+------------+--------------+-------------+--------------+-------------+
1 row in set (0.00 sec)mysql>
比如 varchar(255)现在变成了varchar(85)的时候,恰好这个字段里,长度最大的值是 85 ,对以前的数据没有什么影响 具体为什么 我也不知道
我就把它恢复回去了
你把它 恢复回去吧
我把 DateTime 是 时间 和 字符串 的情况都试了
没有问题啊
mysql> desc ALLCOUNT;
+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| DateTime | varchar(6) | | PRI | | |
| AllKwdCookie | int(10) unsigned | | | 0 | |
| AllKwdCount | int(10) unsigned | | | 0 | |
| AllURLCookie | int(10) unsigned | | | 0 | |
| AllURLCount | int(10) unsigned | | | 0 | |
+--------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
例如恢复下表:
mysql> alter TABLE AAA modify a1 VARCHAR(255), modify a2 VARCHAR(19), modify a3 VARCHAR(255), modify a4 VARCHAR(20) , modify a5 VARCHAR(255);
Query OK, 16404572 rows affected (7 hours 14 min 32.56 sec)
Records: 16404572 Duplicates: 0 Warnings: 0竟然用了7个多小时,我又大概25各这样的表,那就要很长时间了,虽然可以做批处理去执行,但是太慢了!!!!!现在有这样一种方案,叙述如下:
考虑到mysql的表的定义文件放在*.frm的文件中,我不能直接编辑,但是我可以不可以先建立另外一个数据库,把我用到的表在新的库里面建立起来,之后再将新的*.frm 复制到旧的里面去,
将原来的覆盖掉,这种方式不知道师是否可行,请各位大侠指导一二……小弟不胜感激……