This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

If the background

MySQL is a relational database management system. The SQL language used is the most commonly used standardized language for accessing databases. It is characterized by small size, fast speed and low total cost of ownership, especially open source. In Web applications, MySQL is one of the best RDBMS(Relational Database Management System) applications.

This article mainly tells about the compressed version, because the installation is not very convenient, but the function effect is the same!

  • Visit the website of MySQL
  • Download from MySQL

MySQL8 introduction notes

MySQL 8.0 is a very exciting new version of the world’s most popular open source database, with comprehensive improvements. Some key enhancements include:

MySQL8 features

  • SQL window functions, common table expressions, NOWAIT and SKIP LOCKED, descending indexes, grouping, regular expressions, character sets, cost models, and histograms.

  • JSON extended syntax, new features, improved sorting and partial updates. With JSON table functions, you can use the SQL mechanism for JSON data.

  • GIS geographic support. Spatial reference system (SRS), as well as SRS aware spatial data types, spatial indexes and spatial functions.

  • Reliability DDL statements have become atomically and crash safe, and metadata is stored in a single transaction data dictionary. Powered by InnoDB!

  • Observable performance architecture, information architecture, configuration variables, and significant enhancements to error logging.

  • Manageability Remote management, undo tablespace management, and new instant DDL.

  • Security OpenSSL improvements, new default authentication, SQL roles, decomposition super privileges, password strength and more.

  • Performance InnoDB is significantly better for read/write workloads, IO binding workloads and high contention “hot spot” workloads.

  • Added resource group functionality to give users an option to optimize for specific workloads on specific hardware by mapping user threads to cpus

Features developers need to know!

MySQL developers need new features, and MySQL 8.0 offers many new and more needed features in areas such as SQL, JSON, regular expressions, and GIS. Developers also want to be able to store Emojis, so UTF8MB4 is now the default character set in 8.0. Finally, data types have been improved, with bitwise operations on BINARY data types, and improved IPv6 and UUID capabilities.

Here is a brief overview of the noteworthy new features and improvements in MySQL 8.

  1. Performance: MySQL8.0 is twice as fast as MySQL5.7. MySQL 8.0 delivers better performance in read/write workloads, IO intensive workloads, and high contention (“hot spot” hot spot contention issues) workloads.

  1. NoSQL: MySQL has provided NoSQL storage since version 5.7, and this feature has also been improved in version 8.0. This feature eliminates the need for a separate NoSQL document database, while MySQL document storage provides multi-document transaction support and full ACID compliance for JSON documents in schema-less schema.

  1. Window Functions: Since MySQL 8.0, a new concept called Window Functions has been added that can be used to implement several new query methods. The window function is similar to collection functions such as SUM() and COUNT(), but it does not merge multi-row query results into one row, but puts them back into multiple rows. That is, window functions do not need GROUP BY.
    • The window function
      • MySQL 8.0 provides SQL window functionality. Similar to grouped collection functions, window functions perform some calculations on a set of rows, such as COUNT or SUM. However, if the grouped aggregation aggregates this set of rows into one row, the window function performs the aggregation for each row in the result set.
      • Windowed aggregate functions: COUNT, SUM, AVG, MIN, MAX, BIT_OR, BIT_AND, BIT_XOR, STDDEV_POP (and its synonyms STD, STDDEV), STDDEV_SAMP, VAR_POP (and its synonyms VARIANCE), and VAR_SAMP.
      • The specialized window functions are: RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD, and LAG

Support for window functions (aka analysis functions) is a frequent user request

  1. Hide indexes: In MySQL 8.0, indexes can be “hidden” and “show”. When an index is hidden, it is not used by the query optimizer. We can use this feature for performance debugging, such as hiding an index and then observing its impact on the database. If database performance degrades, the index is useful and can be “restored to display”. If the database performance does not change, it indicates that the index is redundant and can be deleted.

  2. Descending indexes: MySQL 8.0 provides support for indexes to be sorted in descending order. Values in such indexes are also sorted in descending order.

  3. Common Table Expressions CTE: When using embedded tables in complex queries, using CTES makes the query statements clearer.

    • MySQL 8.0 provides [recursive] common table expressions (CTES). A non-recursive CTE can be interpreted as an “improved derived table” because it allows derived tables to be referenced multiple times. A recursive CTE is a set of iteratively built rows: starting with the initial set of rows, a process derives new rows, then reinputs those new rows into the process, producing more rows, and so on, until the process does not regenerate rows.

  4. Utf-8 encoding: starting with MySQL 8, utF8MB4 is used as the default MySQL character set.

  5. JSON: MySQL 8 has significantly improved JSON support by adding the JSON_EXTRACT() function to extract data from JSON fields based on the path query parameter. And the JSON_ARRAYAGG() and JSON_OBJECTAGG() aggregation functions for combining data into JSON arrays and objects, respectively.

  6. Reliability: InnoDB now supports atomicity of table DDL, that is, DDL on InnoDB tables can also achieve transaction integrity, either fail rollback, or commit successfully, without the problem of partial success of DDL, in addition to crash-safe feature. Metadata is stored in a single transaction data dictionary.

  7. High Availability: InnoDB clusters provide an integrated native HA solution for your databases.

  8. Security: Improvements to OpenSSL, new default authentication, SQL roles, password strength, authorization.

MySQL8 installation introduction

  • Once on the official website, click on “Dowload” and scroll down

  • The next page looks like this. The link in the red box is mysql Community Edition, which is the free version of mysql. Then we click on the link in this box:

  • Skip to this page, where you can download the Community version of Server:

  • Download installation-free version (other than Windows)

  • At this point, the installation package is downloaded!

Note, the installation directory should be placed in the specified location, secondly, the absolute path to avoid Chinese, recommended preferred English naming conditions!

MySQL decompresses and configures environment variables

  1. Find a folder to put in the decompressed mysql, do not recommend to put in DISK C, about 200M SQL decompressed about 1G

  2. Add the address of the bin file decompressed by MySQL to the path environment variable to facilitate terminal searching for the MySQL command line.

MySQL > create MySQL

After downloading the file, you are advised to decompress it to a directory that does not contain Chinese characters and create the my.ini configuration file in the decompressed directory (if you do not have one, you can manually create one yourself).

Add basic configurations
[client] # set mysql client default character set default-character-set= UTf8mb4 [mysqld] # set 3336 port = 3336 # set mysql installation directory Basedir =D:\developer\installed\mysql8\mysql-8.0.12-winx64 Datadir =D:\developer\installed\mysql8\mysql-8.0.12-winx64\data # Max_connections =20 # Default storage engine to be used when creating new tables Default-storage-engine =INNODB # Number of connection failures allowed. Max_connect_errors =10 # The default character set used by the server is UTF8MB4 character-set-server= UTF8MB4 # The default storage engine that will be used when creating new tables # #mysql_native_password default_authentication_plugin=mysql_native_password #mysql_native_password After this setting, you do not need to manually set the time zone whenever you connect to MySQL. Default-time-zone = '+8:00'Copy the code
  • MySQL8.0.4 = MySQL8.0.4 = MySQL8.0.4 = MySQL8.0.4 = MySQL8.0.4 = MySQL8.0.4 = MySQL8.0.4 = MySQL8.0.4 MySQL used to use “mysql_native_password” for password authentication, but now it uses “caching_sha2_password”.
  • Many database tools and link packages do not support caching_sha2_password. For convenience, you need to switch back to the mysql_native_password authentication plug-in.
Initialize the MySQL service

Use CMD to go to the bin directory after decompression and enter mysqld –initialize –console

  • Mysql generates a data file in the same directory as bin, that is, the database of root, and generates a random password as shown in the red box (if there is no password, use mysql -uroot -p to log in directly). The corresponding account is root. Before changing the password, remember the password for future login.

Installing the MySQL service
  • The command is also executed in the bin directory
Mysqld --install mysqldCopy the code

The default service name is mysql. Of course, if you need to install multiple MySQL services on your computer, you can use different names to distinguish them, such as liboMySQL

  • Run mysqld –install in the bin directory of mysql

    • Error message: Install/Remove of the Service Denied

    • Solution: Select open cmd.exe as administrator.

If the preceding information is displayed, the installation is successful

Start the MySQL service

Enter: net start [service name]

Logging in to the MySQL server
  • Log in to the specified port 3336

The following command is displayed, prompting you to enter the password. Use the random password given during the installation to log in successfully and enter the MySQL command mode.

mysql -u root -p 3336
Copy the code
Change the password

This is a temporary password that needs to be changed.

The ALTER USER input"root"@"localhost" IDENTIFIED BY "New password";
Copy the code

Uninstalling MySQL

Stop the service

Net Stop LibomysQL (Service name)

Unloading service

Mysqld –remove libomysql

The resources

  • www.cnblogs.com/2020javamia…
  • www.jianshu.com/p/647a596cb…
  • www.jb51.net/article/218…
  • Blog.csdn.net/qq_32448349…