I have not, however, been able to find any issues related to the same problem when using the SQL Editor, which does not appear to be affected by the modeler version number. When editing tables through the grid-view (as opposed to typing SQL directly into the editor), you can create millisecond precision DATETIME fields (e.g. DATETIME(3)) but it does not reload the DATETIME columns with the saved precision, but instead simply reloads those column types as DATETIME without any precision value. This normally does not cause a problem if you make future changes to the table that do not involve the DATETIME column(s), but if you do change a DATETIME column (such as renaming the column name, or changing it's order, etc), the generated SQL script will not use the original precision unless you do the "workaround" of explicitly remembering to add the precision back into the type (ie changing DATETIME back to DATETIME(3)). This can be very dangerous if it is somehow missed when updating a table then rolling the changes out to production, as the update will cause all millisecond precision DATETIME data to be unrecoverably lost (unless of course reloading from a previous backup).ġ) Open a schema in MySQL Workbench SQL Editor.ģ) In the grid-view of the table editor, add an INT primary key that is auto-incremented just for good measure.ģ) Now add a field named TEST with a type of DATETIME(3).Ĥ) Apply the changes to actually create the table. Close the table editor tab.Ħ) Query the table rows so that you can manually enter a new row. Enter a new row with " 15:55:25.345" in the TEST column. Apply changes.ħ) Reopen the table editor for the new table, and see that the TEST column type has now reverted to DATETIME.Ĩ) Without fixing the type, change the TEST column name to TEST1. ![]() Apply changes.ĩ) Re-query the table rows and notice that the new row you previously entered now has the value " 15:55:25" in the TEST1 column, with the milliseconds missing. Have the MySQL Workbench SQL Table Editor recognize and load the DATETIME and TIMESTAMP precisions correctly.MySQL Workbench is one of the most popular MySQL database management and monitoring tools for visual database schema design, SQL query building, administration and maintenance. However, if it doesn’t meet your requirements, then here are the top 5 MySQL Workbench alternatives that you can try. Here are the top 5 alternatives to MySQL Workbench. HeidiSQL is one of the best MySQL Workbench alternatives for Windows, for database administration and management. It is free to use and also supports PostgreSQL and SQL Server databases. HeidiSQL makes it easy to create, edit, manage tables, databases, views. It is a very mature and robust tool for MySQL database management.īonus Read : How to Enable Query Cache in MySQL It also supports multiple saved sessions, user management, data export and import, SSH tunneling, procedures, triggers and events. Sequel Pro is one of the most MySQL Workbench alternatives for Mac. ![]() It is a Mac client for MySQL database that provides full MySQL support. You can easily connect Sequel Pro to local as well as remote databases. It is well-documented and easy to install. It allows you to save connections as documents and reuse them easily. Sequel Pro is a fast database application due to its threaded interface. Whether you are Mac developer, Web developer or software programmer, Sequel pro is a useful tool to have.īonus Read : How to Enable Slow Query Log in MySQL It also provides a visual interface to create and run SQL queries, and export results in various formats. Navicat provides various database management tools for MySQL, PostgreSQL and Mongo DB. It allows you to visually model your database, build and run SQL queries, and export results in different formats. Navicat even supports database infrastructure monitoring and administration.
0 Comments
Leave a Reply. |