|
Planet MySQL
|
Planet MySQL - http://planet.mysql.com
|
-
MySQL 8.0: Now documented with Doxygen!
Starting with MySQL release 8.0.0, the source code is now commented using the Doxygen documentation generation tool.
This project is long overdue … it has been a very well-worn issue, or an ever recurring wish, for the last 10 years.
Well, not any more.…
-
MySQL 8.0 (dev): what to look for
This is an unstable release, please don’t use in production.
It was rumored that the new MySQL version would be 8 and not as 5.8 as a lot of people thought, and it appears the rumors were true.
Below are some of the features that caught my eye at first glance:
Roles
Although password expiration was implemented 5.7, the newer version bring a set of collective privileges as a Role. No need to have to copy paste that massive GRANT command you had when creating new users.
UTF-8 as default Charset
This is not yet the default charset coming with the server, but utf8mb4 will be the main charset instead of latin1, and the default collation will change from latin1_swedish_ci to utf8mb4_800_ci_ai. The plan is to do that before General Availability.
Invisible Indexes
Giving an index already exists, you can make it active or inactive. It is a toggling feature which enables the debugging work to see if an index really can be dropped (if it is not being used). This is for the search only, on write operations the index is still maintained.
IPv6 and UUID Manipulation
MySQL do not support those fields natively, however, it is recommended to store those items with the VARBINARY(16) type. MySQL now provides functions to manipulate textual representations of IPv6/UUID and to use bit-wise operations, to test, extract or compare.
Having those function built in, you can use a generated column to index that data.
Source
A more comprehensive list can be found at the MySQL Server Blog. It is worth the read.
™MySQL is a trademark of Oracle.Filed under: Article Tagged: mysql, mysql 8
-
MySQL 8.0 - auto increment feature gets fixed
How InnoDB initializes AUTO_INCREMENT counters is actually not a bug, but a documented mechanism. There were some complaints and even people who lost data over this.
To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.
SELECT MAX(ai_col) FROM table_name FOR
-
Visualizing the MySQL Bug Tide
On the MySQL Bugs website there are some tide stats available. These show rate of bug creation.I've put them in a graph: I made these with this IPython Notebook. There are more detailed graphs per version in the notebook.
-
MySQL DATETIME VS TIMESTAMP
A question which would come sometimes to mind when starting with MySQL is whether I should use DATETIME or TIMESTAMP data type since both appear to store same date and time component.Similarities between datetime and timestamp:1. Values contain both date and time parts.2. Format of retrieval and display is "YYYY-MM-DD HH:MM:SS".3. Can include a trailing fractional seconds part in up to microseconds (6 digits) precision.4. With the fractional part included, the format for these values is "YYYY-MM-DD HH:MM:SS[.fraction]".5. Both the TIMESTAMP and (as of MySQL 5.6.5) DATETIME offer automatic initialization and updating to the current date and time.But both differ in some ways as mentioned below:Differences between DATETIME and TIMESTAMP data types.DATETIME:1. Supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.2. Storage Required Before MySQL 5.6.4 was 8 bytes. *3. Storage Required as of MySQL 5.6.4 is 5 bytes + fractional seconds storage. *4. Preserves the textual representation of the date and time.5. A value in the supported range is saved as it is given to MySQL so lets say if you change the timezone of MySQL, the value remains same that is it stores no timezone information and is timezone independent.Example:By default MySQL uses the SYSTEM timezone as its timezone which is IST in my system.mysql> CREATE TABLE `dt` ( `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_cimysql> SELECT @@time_zone;+-------------+| @@time_zone |+-------------+| SYSTEM |+-------------+mysql> INSERT INTO dt VALUES ('2016-09-12 12:12:00');mysql> SELECT * FROM dt;+---------------------+| dt |+---------------------+| 2016-09-12 12:12:00 |+---------------------+Now lets change the session timezone to CDT which has an offset -05:00 hours with respect to UTC.mysql> SET @@session.time_zone = '-05:00';mysql> SELECT * FROM dt;+---------------------+| dt |+---------------------+| 2016-09-12 12:12:00 |+---------------------+The result above is same irrespective of timezone.TIMESTAMP:1. Supported range is from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. **2. Storage Required Before MySQL 5.6.4 was 4 bytes. *3. Storage Required as of MySQL 5.6.4 is 4 bytes + fractional seconds storage. *4. Preserves values relative to the timezone in use.5. A value in the supported range is saved in UTC timestamp value when the value is supplied to MySQL, so the value contains a timezone reference. While fetching the value again, MySQL will convert that value from UTC to the timezone specific value. If the timezone of MySQL is changed it has no effect on the UTC value stored but when the value is fetched it is displayed as per the current timezone of MySQL and not in the original timezone value which was stored first time. This occurs because the same time zone was not used for conversion in both directions.An example of this would be if timezone of MySQL is currently IST and I save a value of "2016-09-12 12:12:00" into the TIMESTAMP datatype field, so when I fetch this record value from MySQL I will get the same value "2016-09-12 12:12:00". Now if I change the timezone value to CDT and fetch this record value, I will get "2016-09-12 01:42:00" which is the CDT equivalent time of IST "2016-09-12 12:12:00".Example:By default MySQL uses the SYSTEM timezone as its timezone which is IST in my system.mysql> CREATE TABLE `ts` ( `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_cimysql> SELECT @@time_zone;+-------------+| @@time_zone |+-------------+| SYSTEM |+-------------+mysql> INSERT INTO ts VALUES ('2016-09-12 12:12:00');mysql> SELECT * FROM ts;+---------------------+| ts |+---------------------+| 2016-09-12 12:12:00 |+---------------------+Now lets change the session timezone to CDT which has an offset -05:00 hours with respect to UTC.mysql> SET @@session.time_zone = '-05:00';mysql> SELECT * FROM ts;+---------------------+| ts |+---------------------+| 2016-09-12 01:42:00 |+---------------------+The result above is the CDT date time equivalent of IST date time "2016-09-12 12:12:00".References:- https://dev.mysql.com/doc/refman/5.7/en/datetime.html- https://dev.mysql.com/doc/refman/5.6/en/datetime.html- http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp* As of MySQL 5.6.4, storage for YEAR and DATE remains unchanged. However, TIME, DATETIME, and TIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values.https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html** Why is the TIMESTAMP datatype limited to 2038 years and not beyond?- Excerpt from https://en.wikipedia.org/wiki/Year_2038_problem:"The Year 2038 problem is an issue for computing and data storage situations in which time values are stored or calculated as a signed 32-bit integer, and this number is interpreted as the number of seconds since 00:00:00 UTC on 1 January 1970 ("the epoch").[1] Such implementations cannot encode times after 03:14:07 UTC on 19 January 2038, a problem similar to but not entirely analogous to the "Y2K problem" (also known as the "Millennium Bug"), in which 2-digit values representing the number of years since 1900 could not encode the year 2000 or later. Most 32-bit Unix-like systems store and manipulate time in this "Unix time" format, so the year 2038 problem is sometimes referred to as the "Unix Millennium Bug" by association."- It is also known as the Y2K28 bug.A nice illustration from wikipedia showing the Year 2038 bug.Caution: While designing applications please consider the range limitation of TIMESTAMP datatype [1970-2038] before using it or consider using DATETIME datatype instead.
|
Ленты новостей