https://www.sqlines.com/ 2026-04-30T15:52:48+00:00 https://www.sqlines.com/ https://www.sqlines.com/lib/images/favicon.ico text/html 2022-10-21T22:43:44+00:00 https://www.sqlines.com/mysql/auto_increment?rev=1666392224&do=diff AUTO_INCREMENT option allows you to automatically generate unique integer numbers (IDs, identity, sequence) for a column. Quick Example: -- Define a table with an auto-increment column (id starts at 100) CREATE TABLE airlines ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(90) ) AUTO_INCREMENT = 100; -- Insert a row, ID will be automatically generated INSERT INTO airlines (name) VALUES ('United Airlines'); -- Get generated ID SELECT LAST_INSE… text/html 2013-03-30T18:56:35+00:00 https://www.sqlines.com/mysql/drop_referenced_tables?rev=1364669795&do=diff Often during data migration you need to recreate a parent table and reload its data from Oracle to MySQL. The problem that this table already exists in MySQL and has child tables that have foreign keys to the parent table. Let's assume MySQL has the following tables and data after the initial migration: text/html 2017-04-28T05:45:49+00:00 https://www.sqlines.com/mysql/duplicate_entry_for_key_error?rev=1493358349&do=diff When creating a primary key or unique constraint after loading the data, you can get a “Duplicate entry for key 'PRIMARY'” error. If the data in the source database is valid and there are no any duplicates you should check which collation is used in your MySQL database. By default, MySQL uses case-insensitive collation when creating a database, this means that the values that differ only in case are considered as equal. text/html 2017-04-26T15:27:18+00:00 https://www.sqlines.com/mysql/get_db_charset_collation?rev=1493220438&do=diff You can use the following query to see the database character set and collation: -- Read database character sets and collations SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; Sample output: CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH def information_schema utf8 utf8_general_ci NULL def mysql latin1 latin1_swedish_ci NULL def performance_s… text/html 2022-10-22T05:56:27+00:00 https://www.sqlines.com/mysql/regexp_rlike?rev=1666418187&do=diff REGEXP and RLIKE operators check whether the string matches pattern containing a regular expression. Quick Example: -- Find cities that start with A SELECT name FROM cities WHERE name REGEXP '^A'; Overview: Synonyms REGEXP and RLIKE are synonyms Syntax string [NOT] REGEXP pattern Return 1 string matches pattern 0 string does not match pattern NULL string or pattern are NULL Case Sensitivity REGEXP and RLIKE are not case sensitive, except when used for with BINARY and VARB… text/html 2012-04-03T13:46:31+00:00 https://www.sqlines.com/mysql/session_variables?rev=1333460791&do=diff A session variable is a user-defined variable (not a server option) that starts with @, does not require declaration, can be used in any SQL query or statement, not visible to other sessions, and exists until the end of the current session. Quick Example: text/html 2012-04-03T08:41:04+00:00 https://www.sqlines.com/mysql/set_foreign_key_checks?rev=1333442464&do=diff FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables. Quick Example: -- Specify to check foreign key constraints (this is the default) SET FOREIGN_KEY_CHECKS = 1; -- Do not check foreign key constraints SET FOREIGN_KEY_CHECKS = 0; text/html 2011-12-12T13:31:08+00:00 https://www.sqlines.com/mysql/sql_mode?rev=1323696668&do=diff MySQL sql_mode option defines supported SQL syntax, and data validation performed by MySQL. Syntax SET [GLOBAL|SESSION] sql_mode='mode1,mode2, ...' Quick Example SET sql_mode = 'ANSI_QUOTES,PIPES_AS_CONCAT'; Strict Mode When STRICT_TRANS_TABLES or STRICT_ALL_TABLES is specified MySQL Configuration You can set sql_mode in my.cnf (Unix), my.ini (Windows), or --sql-mode (command line) Get and Set sql_mode Retrieving the current value of sql_mode: