timestamp的问题

youcz

mysqldump  -w"xx<'2010-04-16 23:50:12'"
xx是timestamp类型的字段名，是这么写吗？

zeromax

如果是MYSQL 5.0 和5.1是可以的，老版本的不行

ubuntuer

可以的

ruochen

这样可以么？

-bash-3.1# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> desc test.t_timestamp;
+-------+-----------+------+-----+-------------------+----------------+
| Field | Type      | Null | Key | Default           | Extra          |
+-------+-----------+------+-----+-------------------+----------------+
| id    | int(11)   | NO   | PRI | NULL              | auto_increment |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
+-------+-----------+------+-----+-------------------+----------------+
2 rows in set (0.01 sec)

mysql> select * from t_timestamp;
ERROR 1046 (3D000): No database selected
mysql> select * from test.t_timestamp;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2010-04-16 19:21:52 |
|  2 | 2010-04-16 19:21:53 |
|  3 | 2010-04-16 19:21:54 |
|  4 | 2010-04-16 19:22:23 |
|  5 | 0000-00-00 00:00:00 |
|  6 | 2010-04-16 19:22:23 |
+----+---------------------+
6 rows in set (0.00 sec)

mysql> show create table test.t_timestamp;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                        |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_timestamp | CREATE TABLE `t_timestamp` (
  `id` int(11) NOT NULL auto_increment,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table test.t_timestamp\G;
*************************** 1. row ***************************
       Table: t_timestamp
Create Table: CREATE TABLE `t_timestamp` (
  `id` int(11) NOT NULL auto_increment,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> exit
Bye
-bash-3.1# mysqldump test t_timestamp -w "id<=4"
-- MySQL dump 10.11
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.45

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t_timestamp`
--

DROP TABLE IF EXISTS `t_timestamp`;
CREATE TABLE `t_timestamp` (
  `id` int(11) NOT NULL auto_increment,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t_timestamp`
--
-- WHERE:  id<=4

LOCK TABLES `t_timestamp` WRITE;
/*!40000 ALTER TABLE `t_timestamp` DISABLE KEYS */;
INSERT INTO `t_timestamp` VALUES (1,'2010-04-16 11:21:52'),(2,'2010-04-16 11:21:53'),(3,'2010-04-16 11:21:54'),(4,'2010-04-16 11:22:23');
/*!40000 ALTER TABLE `t_timestamp` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2010-04-16 11:31:53


-bash-3.1# mysqldump test t_timestamp -w "ts<='2010-04-16 19:21:54'"
-- MySQL dump 10.11
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.45

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t_timestamp`
--

DROP TABLE IF EXISTS `t_timestamp`;
CREATE TABLE `t_timestamp` (
  `id` int(11) NOT NULL auto_increment,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t_timestamp`
--
-- WHERE:  ts<='2010-04-16 19:21:54'

LOCK TABLES `t_timestamp` WRITE;
*!40000 ALTER TABLE `t_timestamp` DISABLE KEYS */;
INSERT INTO `t_timestamp` VALUES (1,'2010-04-16 11:21:52'),(2,'2010-04-16 11:21:53'),(3,'2010-04-16 11:21:54'),(4,'2010-04-16 11:22:23'),(5,'0000-00-00 00:00:00'),(6,'2010-04-16 11:22:23');
/*!40000 ALTER TABLE `t_timestamp` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2010-04-16 11:32:00

youcz

回复 4# ruochen


    -- WHERE:  ts<='2010-04-16 19:21:00'

把条件改成这个试下，可以吗？

youcz

回复 5# youcz


    在我的测试中ts<='2010-04-16 19:21:54'格式的条件是不起作用的。

youcz

回复 6# youcz


    哦，时区问题，谢谢各位