mysql5.0+ hint field does not have a default value (doesn ' t has the solution for a default value)

Source: Internet
Author: User
Tags numeric value

  method One:Open My.ini, find  sql-mode= "strict_trans_tables,no_auto_create_user,no_engine_substitution" modified to  sql-mode= " No_auto_create_user,no_engine_substitution "then restart MySQL. Method Two:  mysql 5 uses a strict mode which needs to be disabled. In Windows, Goto start–>programs–>mysql->mysql Instance Config wizard.follow through the Reconfigure Instance op tion–>detailed Configuration–>continue Next a few screens. At the bottom under Enable TCP/IP option there is ' Enable Strict Mode '. Deslect This option (no tick). Save changes and MySQL would restart.   turn on static mode in MySQL5 Configuration Wizard (Enable Strict mode). This method has not been tried! :) method Three: Before executing the insert name (usually this error appears in the insert operation) First execute: SET sql_mode= ", such as: Conn.execute (" set sql_mode= ") and then insert the statement will not have an error! Method Four: This method is very troublesome, that is, the prompt for the wrong field property "Allow null" is set to Yes. This also solves the problem, but if the field is a lot then it will be troublesome.   The following references are departments for MySQL mode:
  Before MySQL 5.0.2, MySQL was not strict with illegal or improper values, and forced them into legal values for data entry. In MySQL 5.0.2 and later, the previous default behavior is preserved, but you can choose a more traditional processing method for bad values, allowing the server to reject and discard statements that have bad values.  This section describes the default behavior of MySQL (leniency behavior), the new strict SQL schema, and their differences. If you are not using strict mode, the following conditions are true. If you insert an "incorrect" value into a column, such as inserting a null value into a non-null column, or inserting an oversized numeric value into a numeric column, MySQL sets these columns to the "most likely value" instead of generating an error message. • If you attempt to save a range of values to a numeric column, the MySQL server will save 0 (the smallest possible value) instead, or the maximum possible value. • For a string, MySQL either saves an empty string, or saves as many parts of the string to the column. • If you plan to save a string that does not begin with a numeric value to a numeric column, MySQL will save 0. MySQL allows specific incorrect date values to be saved to date and datetime columns (such as "2000-02-31" or "2000-02-00"). The point is that the validation date is not a task for SQL Server. If MySQL can save the date value and retrieve the same value exactly, MySQL will be able to save it at the given value. If the date is completely incorrect (exceeding the range that the server can save), the special date value "0000-00-00" will be saved in the column instead. • If you attempt to save a null value to a column that does not accept null values, an error occurs for a single-line INSERT statement. For multi-line INSERT statements or INSERT INTO ... Select statement, the MySQL server saves the implied default value for the column data type. In general, for a numeric type, it is 0, for a string type, it is an empty string ("), and is" zero "for the date and time type. If the INSERT statement does not specify a value for the column, MySQL inserts a default value if the column definition contains an explicit default clause.  If there is no such default clause in the definition, MySQL inserts the implied default value of the column data type. The reason for using the preceding rules is that these conditions cannot be checked until the statement begins execution. If you encounter such problems after updating several lines, we cannot rely on rollback only, because the storage engine may not support rollback. The abort statement is not a good choice, in which case the update completes "half", which may be the worst case scenario.  For this example, a better approach is to "just be the best" and then continue as if nothing had happened. In MySQL 5.0.2 and laterIn this section, you can use the strict_trans_tables or Strict_all_tables SQL mode to choose a more restrictive approach. How the Strict_trans_tables works: · For the transactional storage engine, bad data values that occur anywhere in the statement cause the statement to be discarded and rollback is performed. For non-transactional storage engines, the statement is discarded if the error appears on line 1th to be inserted or updated. (In this case, you can assume that the statement did not change the table, just like the transaction table). Errors that occur after the first line do not cause a discard statement. Instead, the bad data values are adjusted and alarms are given, not errors.  In other words, after using Strict_trans_tables, the error value causes MySQL to perform a rollback operation, and if so, all updates are done. To perform a more rigorous check, enable strict_all_tables. In addition to the non-transactional storage engine, it is equivalent to strict_trans_tables, even when bad data occurs in other rows after the first row, resulting in an error that results in a discard statement. This means that if the error is in the middle of a multi-line insert or update process for non-transactional tables, only partial results are updated. The preceding row completes the insert or update, but the line following the point of the error does not. For non-transactional tables, to avoid this, use a single-line statement, or use Strict_trans_tables if you can accept a conversion warning instead of an error. To prevent the problem from appearing in the 1th field, do not use MySQL to check the contents of the column.  The safest way (usually faster) is to have the application responsible for passing only valid values to the database. With strict mode options, you can use Insert ignore or update ignore instead of INSERT or update without ignore to treat errors as alarms.

mysql5.0+ hint field does not have a default value (doesn ' t has the '-default value ') Workaround

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.