The MySQL server maintains several time zone settings:
- System time zone. When the server starts, it attempts to determine the time zone of the host machine and uses it to set the
system_time_zone system variable.
- The server’s time zone. The global
time_zone system variable. If you have SUPER privileges, you may:
SET GLOBAL time_zone = timezone;. You may use:
SELECT @@global.time_zone; to get its current value.
- Per-connection time zones. Initially takes its value from
time_zone, but the client can change its own time zone with:
SET time_zone = timezone;. You may use:
SELECT @@session.time_zone; to get its current value.
The ‘timezone’ values format:
'SYSTEM' means: Same as the system’s time zone (read above).
- A string indicating an offset from UTC, such as
'-6:00'. So for example, to set your current connection’s time zone to UTC, use:
SET time_zone='+00:00'; (and if you change your mind, revert to using
'SYSTEM' time zone using:
- Named time zone, such as
'MET'. Can be used only if the time zone information tables in the mysql database have been created and populated!
More info at MySQL documentation here
Some useful notes about MySQL’s AUTO_INCREMENT value:
- Available only for those tables where one column uses AUTO_INCREMENT. See a very clear example for a table that uses AUTO_INCREMENT in MySQL documentation here.
- To find what the current AUTO_INCREMENT value of some table is, you should NOT rely on the rows count, as some threads in StackOverflow suggest. Instead,
SHOW TABLE STATUS LIKE `yourtablename`;
will reveal some info on your table. One of the columns will be “AUTO_INCREMENT”, with the value you’re looking for: The next time a row is inserted, the field with the AUTO_INCREMENT property will get this value. For example, this value is initially 1 when creating the table, and also after a TRUNCATE.
- To get only the AUTO_INCREMENT value:
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name=`yourtablename` AND table_schema = DATABASE();
- To compare with the number of rows (say, to verify that the AUTO_INCREMENT value equals to the number of rows plus 1), consider:
SELECT TABLE_ROWS, AUTO_INCREMENT FROM information_schema.tables WHERE table_name=`yourtablename` AND table_schema = DATABASE();
- To modify the AUTO_INCREMENT value yourself (say, to the number 123), you need:
ALTER TABLE `yourtablename` AUTO_INCREMENT = 123;
- A hack: To “reset” the AUTO_INCREMENT value yourself to the lowest possible number, try:
ALTER TABLE `yourtablename` AUTO_INCREMENT = 1;
- Last notes about ‘SHOW TABLE STATUS’: Yes, it has a weird syntax. Also, don’t get startled if the
TABLE_ROWS column shows a different number of rows than you actually have in that table (i.e. when calling
SELECT COUNT(1) FROM `yourtablename`, for example).