MySQL：在 my.cnf 选项文件中设置 time_zone
在 MySQL 中，您可以设置名为 time_zone 的会话变量来更改时区。例如，当查看来自另一个国家/地区的时间戳时，这很有用。这是一个示例：
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2010-12-30 18:59:18 |
+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone='Brazil/East';
Query OK, 0 rows affected (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2010-12-30 09:59:29 |
+---------------------+
1 row in set (0.00 sec)
是否可以将其放入选项文件中，例如 .my.cnf ？
当我尝试时，它不起作用。我得到的只是：
mysql：未知变量“time_zone=巴西/东部”
In MySQL you can set a session variable called time_zone to change the timezone. This is useful e.g. when looking at timestamps from another country. Here is an example:
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2010-12-30 18:59:18 |
+---------------------+
1 row in set (0.00 sec)
mysql> set time_zone='Brazil/East';
Query OK, 0 rows affected (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2010-12-30 09:59:29 |
+---------------------+
1 row in set (0.00 sec)
Is it possible to put that in an option file e.g. .my.cnf ?
When I try, it doesn't work. All I get is:
mysql: unknown variable 'time_zone=Brazil/East'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
它应该是
详细信息：http://dev. mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_time_zone
it should be
details : http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_time_zone
我不确定 Xampp 中发生了什么变化，但只有当您将此行放在正确的位置时，此解决方案才有效。相信我，我尝试了很多次，并且必须进行相当彻底的搜索才能找到这个解决方案。
示例：
https://community.apachefriends.org/f/viewtopic .php?f=16&t=47656
另外，如果您要使用“America/Los_Angeles”，您需要确保数据库中填充了正确的时区名称。我建议使用偏移量。我实际上建议使用 UTC 作为您的基础，然后根据用户的时区从该时间点转换您的时间，这将为您节省以后的许多麻烦，并保持您的数据库良好和统一。查看我在下面链接的指南，它为我解释得非常清楚，并且我使用了这个系统。有很多方法可以对其进行编码，但采用这种方法可以避免很多问题。
http:// /www.vertabelo.com/blog/technical-articles/the-proper-way-to-handle-multiple-time-zones-in-mysql
I'm not certain what has changed in Xampp, but this solution only works if you place this line in the proper place. Trust me I tried many times and had to do a pretty thorough search to find this solution.
Example:
https://community.apachefriends.org/f/viewtopic.php?f=16&t=47656
Also, you'll want to be sure that you have your database populated with the proper time zone names if you are going to use "America/Los_Angeles". I'd recommend using the offset. I'd actually recommend using UTC as your base then converting your time from that point for users based on their timezone which will save you many headaches later and keep your database nice and uniform. Check out the guide I linked below it explained it very clearly for me and I utilized this system. There are many ways to code it but taking this approach will save you a lot of issues.
http://www.vertabelo.com/blog/technical-articles/the-proper-way-to-handle-multiple-time-zones-in-mysql
在
~/.my.cnf中：In
~/.my.cnf:对于 MAMP，我在
/Applications/MAMP/conf/my.cnf的[mysqld]下添加了default_time_zone=-03:00我会得到巴西/东部出现以下错误，可能是因为其已弃用（https://en.wikipedia.org/wiki /List_of_tz_database_time_zones):
[错误] 致命错误：非法或未知的默认时区“巴西/东部”For MAMP, I added
default_time_zone=-03:00under[mysqld]in/Applications/MAMP/conf/my.cnfI would get the following error for Brazil/East, probably because its deprecated(https://en.wikipedia.org/wiki/List_of_tz_database_time_zones):
[ERROR] Fatal error: Illegal or unknown default time zone 'Brazil/East'虽然有点晚了，但这可能还是有帮助的：
显式设置时区时，请确认您使用的是正确的时区名称，请记住许多时区名称已被弃用。您可以使用 https://en.wikipedia.org/wiki/List_of_tz_database_time_zones 进行确认。
就我而言，使用 MySQL 5.7，将已弃用的时区名称添加到我的 mysqld.cnf 文件中的 [mysqld] 下面时不起作用。使用新的时区名称并重新启动 mysql 服务，工作正常。
因此，对于这里的用户 @kev，使用 America/Sao_Paulo 应该有效，而不是使用 Brazil/East。
A bit late, but this might be helpful nonetheless:
When explicitly setting the timezone, confirm that you are using the correct timezone name, keeping in mind the many have been deprecated. You can use https://en.wikipedia.org/wiki/List_of_tz_database_time_zones to confirm.
In my case, using MySQL 5.7, the deprecated timezone name was not working when adding it below the [mysqld] in my mysqld.cnf file. Using the new timezone name, and restarting the mysql service, worked.
So for user @kev here, using America/Sao_Paulo should work, instead of using Brazil/East.
编辑以下内容：
MySQL.cnf：
请参阅此处了解当前时区格式 --> https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
Edit the following:
MySQL.cnf:
See here for current Time Zone formats --> https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
对于 Windows 10：
转到
C:\ProgramData\MySQL\MySQL Server 8.0文件夹并以管理员身份打开my.ini文件。滚动到
[mysqld]: 行。在
[mysqld]之后添加以下行：保存文件并从
Services窗口重新启动MySQL80服务。您可以控制更改
For Windows 10:
Go to
C:\ProgramData\MySQL\MySQL Server 8.0folder and openmy.inifile as Administrator.Scroll to the
[mysqld]: line.Add this lines after the
[mysqld]:Save the file and restart the
MySQL80service fromServiceswindow.You can control changes from