在使用 MySQL/PHP 的字段中使用 now() 插入当前日期/时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9841336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert current date/time using now() in a field using MySQL/PHP
提问by user1260310
Since MySQL evidently cannot automatically insert the function now() in a datetime field in adding new records like some other databases, based on comments, I'm explicitly trying to insert it using an SQL statement. (People seem to think timestamp with curdate() is not the answer due to the various limitations of timestamp.) There are numerous articles on the web suggesting inserting now() using SQL should work.
由于 MySQL 显然无法像其他一些数据库一样在添加新记录时自动将函数 now() 插入到日期时间字段中，因此我明确地尝试使用 SQL 语句插入它。（由于时间戳的各种限制，人们似乎认为带有 curdate() 的时间戳不是答案。）网上有许多文章建议使用 SQL 插入 now() 应该可以工作。
When I try to insert the date time using the SQL statement, however, the field does not populate with the current time/date, but it only gives me the default 0000-00-, etc. This is probably a syntax error, but it's driving me crazy, so I am posting it.
然而，当我尝试使用 SQL 语句插入日期时间时，该字段不会填充当前时间/日期，但它只给我默认的 0000-00- 等。这可能是一个语法错误，但它是把我逼疯了，所以我发布了它。
mysql_query("INSERT INTO users (first, last, whenadded) VALUES ('$first', '$last', now())";
It inserts first and last, but nothing for when added, leaving 0000-00-00, etc. in the whenadded field.
它首先插入和最后插入，但在添加时不插入，在添加时字段中留下 0000-00-00 等。
The field type is datetime, it has no collation, attributes, null default or extra. BTW, I tried putting now() in single quotes... It threw an error.
字段类型是日期时间，它没有排序规则、属性、空默认值或额外的。顺便说一句，我尝试将 now() 放在单引号中......它抛出了一个错误。
回答by Ronan
NOW() normally works in SQL statements and returns the date and time. Check if your database field has the correct type (datetime). Otherwise, you can always use the PHPdate() function and insert:
NOW() 通常在 SQL 语句中工作并返回日期和时间。检查您的数据库字段是否具有正确的类型（日期时间）。否则，您始终可以使用PHPdate() 函数并插入：
date('Y-m-d H:i:s')
But I wouldn't recommend this.
但我不会推荐这个。
回答by ckwolfling
You forgot to close the mysql_query command:
您忘记关闭 mysql_query 命令：
mysql_query("INSERT INTO users (first, last, whenadded) VALUES ('$first', '$last', now())");
mysql_query("INSERT INTO users (first, last, whenadded) VALUES ('$first', '$last', now())");
Note that last parentheses.
注意最后一个括号。
回答by VolkerK
Like Pekka said, it should work this way. I can't reproduce the problem with this self-contained example:
就像 Pekka 说的，它应该这样工作。我无法用这个独立的例子重现这个问题：
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->exec('
CREATE TEMPORARY TABLE soFoo (
id int auto_increment,
first int,
last int,
whenadded DATETIME,
primary key(id)
)
');
$pdo->exec('INSERT INTO soFoo (first,last,whenadded) VALUES (0,1,Now())');
$pdo->exec('INSERT INTO soFoo (first,last,whenadded) VALUES (0,2,Now())');
$pdo->exec('INSERT INTO soFoo (first,last,whenadded) VALUES (0,3,Now())');
foreach( $pdo->query('SELECT * FROM soFoo', PDO::FETCH_ASSOC) as $row ) {
echo join(' | ', $row), "\n";
}
Which (currently) prints
哪个（当前）打印
1 | 0 | 1 | 2012-03-23 16:00:18
2 | 0 | 2 | 2012-03-23 16:00:18
3 | 0 | 3 | 2012-03-23 16:00:18
And here's (almost) the same script using a TIMESTAMP field and DEFAULT CURRENT_TIMESTAMP:
这是（几乎）使用 TIMESTAMP 字段和 DEFAULT CURRENT_TIMESTAMP 的相同脚本：
<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->exec('
CREATE TEMPORARY TABLE soFoo (
id int auto_increment,
first int,
last int,
whenadded TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
primary key(id)
)
');
$pdo->exec('INSERT INTO soFoo (first,last) VALUES (0,1)');
$pdo->exec('INSERT INTO soFoo (first,last) VALUES (0,2)');
sleep(1);
$pdo->exec('INSERT INTO soFoo (first,last) VALUES (0,3)');
foreach( $pdo->query('SELECT * FROM soFoo', PDO::FETCH_ASSOC) as $row ) {
echo join(' | ', $row), "\n";
}
Conveniently, the timestamp is converted to the same datetime string representation as in the first example - at least with my PHP/PDO/mysqlnd version.
方便的是，时间戳被转换为与第一个示例中相同的日期时间字符串表示 - 至少在我的 PHP/PDO/mysqlnd 版本中。
回答by Your Common Sense
The only reason I can think of is you are adding it as string 'now()', not function call now().
Or whatever else typo.
我能想到的唯一原因是您将它添加为字符串'now()'，而不是函数调用now()。
或其他任何错字。
SELECT NOW();
to see if it returns correct value?
看看它是否返回正确的值？
回答by Benas
What about SYSDATE() ?
SYSDATE() 呢？
<?php
$db = mysql_connect('localhost','user','pass');
mysql_select_db('test_db');
$stmt = "INSERT INTO `test` (`first`,`last`,`whenadded`) VALUES ".
"('{$first}','{$last}','SYSDATE())";
$rslt = mysql_query($stmt);
?>
Look at Difference between NOW(), SYSDATE() & CURRENT_DATE() in MySQLfor more info about NOW() and SYSDATE().
查看MySQL 中 NOW()、SYSDATE() 和 CURRENT_DATE() 之间的差异以了解有关 NOW() 和 SYSDATE() 的更多信息。
回答by Shuhad zaman
now()
现在（）
worked for me . but my field type is dateonly and yours is datetime. i am not sure if this is the case
为我工作。但我的字段类型仅为date ，而您的字段类型为datetime。我不确定是否是这种情况
回答by DismissedAsDrone
These both work fine for me...
这两个对我来说都很好...
<?php
$db = mysql_connect('localhost','user','pass');
mysql_select_db('test_db');
$stmt = "INSERT INTO `test` (`first`,`last`,`whenadded`) VALUES ".
"('{$first}','{$last}','NOW())";
$rslt = mysql_query($stmt);
$stmt = "INSERT INTO `users` (`first`,`last`,`whenadded`) VALUES ".
"('{$first}', '{$last}', CURRENT_TIMESTAMP)";
$rslt = mysql_query($stmt);
?>
Side note: mysql_query() is not the best way to connect to MySQL in current versions of PHP.
旁注：mysql_query() 不是在当前版本的 PHP 中连接到 MySQL 的最佳方式。
回答by Jodyshop
Currently, and with the new versions of Mysql can insert the current dateautomatically without adding a code in your PHP file. You can achieve that from Mysql while setting up your database as follows:
目前，使用新版本的 Mysql 可以自动插入当前日期，而无需在 PHP 文件中添加代码。您可以在设置数据库时从 Mysql 实现，如下所示：
Now, any new post will automatically get a unique date and time. Hope this can help.
现在，任何新帖子都会自动获得唯一的日期和时间。希望这能有所帮助。
