Shulou(Shulou.com)06/02 Report--

This article will explain in detail the example analysis of the mysql command set. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Sql_mode defines sql syntax, data validation, etc., that should be supported by mysql.

Select @ @ sql_mode Property description ONLY_FULL_GROUP_BY for GROUP BY aggregation operations, if the column in SELECT does not appear in GROUP BY, then the SQL will be considered illegal because the column is not in the GROUP BY clause STRICT_TRANS_TABLES if a value cannot be inserted into a transaction table, the current operation is interrupted, the non-transaction table is not restricted to NO_ZERO_IN_DATE in strict mode, and dates with month or day parts of 0 are not accepted. If we use the IGNORE option, we insert '0000-00-00' for similar dates. In non-strict mode, the date can be accepted, but a warning is generated. NO_ZERO_DATE is in strict mode, do not use '0000-00' as the legal date. You can still insert zero dates with the IGNORE option. In non-strict mode, the date can be accepted, but a warning ERROR_FOR_DIVISION_BY_ZERO is generated in strict mode, and an error (otherwise a warning) is generated if divided by zero (or MOD (XJie 0)) during the INSERT or UPDATE process. If the pattern is not given, MySQL returns NULL when divided by zero. If used in INSERT IGNORE or UPDATE IGNORE, MySQL generation is divided by zero warning, but the result is NULLNO_AUTO_CREATE_USER to prevent GRANT from automatically creating new users unless a password is also specified. NO_ENGINE_SUBSTITUTION throws an error if the required storage engine is disabled or uncompiled. When this value is not set, it is replaced with the default storage engine and an exception is thrown

Sql_mode mode

The pattern describes the ANSI loose pattern, changing the syntax and behavior to make it more compliant with the standard SQL. Check the inserted data, if it does not meet the defined type or length, adjust or truncate the data type and save it, and report a warning warning. For the errors mentioned at the beginning of this article, you can first set sql_mode to ANSI mode so that you can insert data, and for the field value of the result divided by 0, the database will replace it with a null value. TRADITIONAL strict mode, when inserting data into the mysql database, strictly verifies the data to ensure that the wrong data can not be inserted, and reports error errors, not just warnings. When used in things, things will be rolled back. Note: discard INSERT/UPDATE as soon as an error is found. If you use a non-transactional storage engine, this approach is not what you want, because the data changes made before the error are not scrolled, and the result is that the update is "only partially carried out". STRICT_TRANS_TABLES strict mode, strict verification of data, error data can not be inserted, report error error. If the given value cannot be inserted into the transaction table, the statement is discarded. For a non-transactional table, if the value appears in the first row of a single-line statement or multiple-row statement, the statement is discarded.

Data sheet

Mode description show index query table index desc query table structure lock processing command description show OPEN TABLES where In_use > 0; query whether to lock table show processlist; view all processes show full processlist

Kill kills the process number of the specified mysql connection SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; view the transaction being locked SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; view the transaction waiting for lock show engine innodb status; view the runtime information of the innodb engine show status like'% lock%'; view the server status show variables like'% timeout%' Check the timeout on the "sample analysis of mysql command set" this article shares here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.