1<?php 2// This file is part of Moodle - http://moodle.org/ 3// 4// Moodle is free software: you can redistribute it and/or modify 5// it under the terms of the GNU General Public License as published by 6// the Free Software Foundation, either version 3 of the License, or 7// (at your option) any later version. 8// 9// Moodle is distributed in the hope that it will be useful, 10// but WITHOUT ANY WARRANTY; without even the implied warranty of 11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12// GNU General Public License for more details. 13// 14// You should have received a copy of the GNU General Public License 15// along with Moodle. If not, see <http://www.gnu.org/licenses/>. 16 17/** 18 * Mysql specific SQL code generator. 19 * 20 * @package core_ddl 21 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com 22 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com 23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 24 */ 25 26defined('MOODLE_INTERNAL') || die(); 27 28require_once($CFG->libdir.'/ddl/sql_generator.php'); 29 30/** 31 * This class generate SQL code to be used against MySQL 32 * It extends XMLDBgenerator so everything can be 33 * overridden as needed to generate correct SQL. 34 * 35 * @package core_ddl 36 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com 37 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com 38 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 39 */ 40class mysql_sql_generator extends sql_generator { 41 42 // Only set values that are different from the defaults present in XMLDBgenerator 43 44 /** @var string Used to quote names. */ 45 public $quote_string = '`'; 46 47 /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/ 48 public $default_for_char = ''; 49 50 /** @var bool To specify if the generator must use some DEFAULT clause to drop defaults.*/ 51 public $drop_default_value_required = true; 52 53 /** @var string The DEFAULT clause required to drop defaults.*/ 54 public $drop_default_value = null; 55 56 /** @var string To force primary key names to one string (null=no force).*/ 57 public $primary_key_name = ''; 58 59 /** @var string Template to drop PKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/ 60 public $drop_primary_key = 'ALTER TABLE TABLENAME DROP PRIMARY KEY'; 61 62 /** @var string Template to drop UKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/ 63 public $drop_unique_key = 'ALTER TABLE TABLENAME DROP KEY KEYNAME'; 64 65 /** @var string Template to drop FKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/ 66 public $drop_foreign_key = 'ALTER TABLE TABLENAME DROP FOREIGN KEY KEYNAME'; 67 68 /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/ 69 public $sequence_extra_code = false; 70 71 /** @var string The particular name for inline sequences in this generator.*/ 72 public $sequence_name = 'auto_increment'; 73 74 public $add_after_clause = true; // Does the generator need to add the after clause for fields 75 76 /** @var string Characters to be used as concatenation operator.*/ 77 public $concat_character = null; 78 79 /** @var string The SQL template to alter columns where the 'TABLENAME' and 'COLUMNSPECS' keywords are dynamically replaced.*/ 80 public $alter_column_sql = 'ALTER TABLE TABLENAME MODIFY COLUMN COLUMNSPECS'; 81 82 /** @var string SQL sentence to drop one index where 'TABLENAME', 'INDEXNAME' keywords are dynamically replaced.*/ 83 public $drop_index_sql = 'ALTER TABLE TABLENAME DROP INDEX INDEXNAME'; 84 85 /** @var string SQL sentence to rename one index where 'TABLENAME', 'OLDINDEXNAME' and 'NEWINDEXNAME' are dynamically replaced.*/ 86 public $rename_index_sql = null; 87 88 /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/ 89 public $rename_key_sql = null; 90 91 /** Maximum size of InnoDB row in Antelope file format */ 92 const ANTELOPE_MAX_ROW_SIZE = 8126; 93 94 /** 95 * Reset a sequence to the id field of a table. 96 * 97 * @param xmldb_table|string $table name of table or the table object. 98 * @return array of sql statements 99 */ 100 public function getResetSequenceSQL($table) { 101 102 if ($table instanceof xmldb_table) { 103 $tablename = $table->getName(); 104 } else { 105 $tablename = $table; 106 } 107 108 // From http://dev.mysql.com/doc/refman/5.0/en/alter-table.html 109 $value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}'); 110 $value++; 111 return array("ALTER TABLE $this->prefix$tablename AUTO_INCREMENT = $value"); 112 } 113 114 /** 115 * Calculate proximate row size when using InnoDB 116 * tables in Antelope row format. 117 * 118 * Note: the returned value is a bit higher to compensate for 119 * errors and changes of column data types. 120 * 121 * @deprecated since Moodle 2.9 MDL-49723 - please do not use this function any more. 122 */ 123 public function guess_antolope_row_size(array $columns) { 124 throw new coding_exception('guess_antolope_row_size() can not be used any more, please use guess_antelope_row_size() instead.'); 125 } 126 127 /** 128 * Calculate proximate row size when using InnoDB tables in Antelope row format. 129 * 130 * Note: the returned value is a bit higher to compensate for errors and changes of column data types. 131 * 132 * @param xmldb_field[]|database_column_info[] $columns 133 * @return int approximate row size in bytes 134 */ 135 public function guess_antelope_row_size(array $columns) { 136 137 if (empty($columns)) { 138 return 0; 139 } 140 141 $size = 0; 142 $first = reset($columns); 143 144 if (count($columns) > 1) { 145 // Do not start with zero because we need to cover changes of field types and 146 // this calculation is most probably not be accurate. 147 $size += 1000; 148 } 149 150 if ($first instanceof xmldb_field) { 151 foreach ($columns as $field) { 152 switch ($field->getType()) { 153 case XMLDB_TYPE_TEXT: 154 $size += 768; 155 break; 156 case XMLDB_TYPE_BINARY: 157 $size += 768; 158 break; 159 case XMLDB_TYPE_CHAR: 160 $bytes = $field->getLength() * 3; 161 if ($bytes > 768) { 162 $bytes = 768; 163 } 164 $size += $bytes; 165 break; 166 default: 167 // Anything else is usually maximum 8 bytes. 168 $size += 8; 169 } 170 } 171 172 } else if ($first instanceof database_column_info) { 173 foreach ($columns as $column) { 174 switch ($column->meta_type) { 175 case 'X': 176 $size += 768; 177 break; 178 case 'B': 179 $size += 768; 180 break; 181 case 'C': 182 $bytes = $column->max_length * 3; 183 if ($bytes > 768) { 184 $bytes = 768; 185 } 186 $size += $bytes; 187 break; 188 default: 189 // Anything else is usually maximum 8 bytes. 190 $size += 8; 191 } 192 } 193 } 194 195 return $size; 196 } 197 198 /** 199 * Given one correct xmldb_table, returns the SQL statements 200 * to create it (inside one array). 201 * 202 * @param xmldb_table $xmldb_table An xmldb_table instance. 203 * @return array An array of SQL statements, starting with the table creation SQL followed 204 * by any of its comments, indexes and sequence creation SQL statements. 205 */ 206 public function getCreateTableSQL($xmldb_table) { 207 // First find out if want some special db engine. 208 $engine = $this->mdb->get_dbengine(); 209 // Do we know collation? 210 $collation = $this->mdb->get_dbcollation(); 211 212 // Do we need to use compressed format for rows? 213 $rowformat = ""; 214 $size = $this->guess_antelope_row_size($xmldb_table->getFields()); 215 if ($size > self::ANTELOPE_MAX_ROW_SIZE) { 216 if ($this->mdb->is_compressed_row_format_supported()) { 217 $rowformat = "\n ROW_FORMAT=Compressed"; 218 } 219 } 220 221 $utf8mb4rowformat = $this->mdb->get_row_format_sql($engine, $collation); 222 $rowformat = ($utf8mb4rowformat == '') ? $rowformat : $utf8mb4rowformat; 223 224 $sqlarr = parent::getCreateTableSQL($xmldb_table); 225 226 // This is a very nasty hack that tries to use just one query per created table 227 // because MySQL is stupidly slow when modifying empty tables. 228 // Note: it is safer to inject everything on new lines because there might be some trailing -- comments. 229 $sqls = array(); 230 $prevcreate = null; 231 $matches = null; 232 foreach ($sqlarr as $sql) { 233 if (preg_match('/^CREATE TABLE ([^ ]+)/', $sql, $matches)) { 234 $prevcreate = $matches[1]; 235 $sql = preg_replace('/\s*\)\s*$/s', '/*keyblock*/)', $sql); 236 // Let's inject the extra MySQL tweaks here. 237 if ($engine) { 238 $sql .= "\n ENGINE = $engine"; 239 } 240 if ($collation) { 241 if (strpos($collation, 'utf8_') === 0) { 242 $sql .= "\n DEFAULT CHARACTER SET utf8"; 243 } 244 $sql .= "\n DEFAULT COLLATE = $collation "; 245 } 246 if ($rowformat) { 247 $sql .= $rowformat; 248 } 249 $sqls[] = $sql; 250 continue; 251 } 252 if ($prevcreate) { 253 if (preg_match('/^ALTER TABLE '.$prevcreate.' COMMENT=(.*)$/s', $sql, $matches)) { 254 $prev = array_pop($sqls); 255 $prev .= "\n COMMENT=$matches[1]"; 256 $sqls[] = $prev; 257 continue; 258 } 259 if (preg_match('/^CREATE INDEX ([^ ]+) ON '.$prevcreate.' (.*)$/s', $sql, $matches)) { 260 $prev = array_pop($sqls); 261 if (strpos($prev, '/*keyblock*/')) { 262 $prev = str_replace('/*keyblock*/', "\n, KEY $matches[1] $matches[2]/*keyblock*/", $prev); 263 $sqls[] = $prev; 264 continue; 265 } else { 266 $sqls[] = $prev; 267 } 268 } 269 if (preg_match('/^CREATE UNIQUE INDEX ([^ ]+) ON '.$prevcreate.' (.*)$/s', $sql, $matches)) { 270 $prev = array_pop($sqls); 271 if (strpos($prev, '/*keyblock*/')) { 272 $prev = str_replace('/*keyblock*/', "\n, UNIQUE KEY $matches[1] $matches[2]/*keyblock*/", $prev); 273 $sqls[] = $prev; 274 continue; 275 } else { 276 $sqls[] = $prev; 277 } 278 } 279 } 280 $prevcreate = null; 281 $sqls[] = $sql; 282 } 283 284 foreach ($sqls as $key => $sql) { 285 $sqls[$key] = str_replace('/*keyblock*/', "\n", $sql); 286 } 287 288 return $sqls; 289 } 290 291 /** 292 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add the field to the table. 293 * 294 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 295 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 296 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 297 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 298 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 299 * @return array The SQL statement for adding a field to the table. 300 */ 301 public function getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 302 $sqls = parent::getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause); 303 304 if ($this->table_exists($xmldb_table)) { 305 $tablename = $xmldb_table->getName(); 306 307 $size = $this->guess_antelope_row_size($this->mdb->get_columns($tablename)); 308 $size += $this->guess_antelope_row_size(array($xmldb_field)); 309 310 if ($size > self::ANTELOPE_MAX_ROW_SIZE) { 311 if ($this->mdb->is_compressed_row_format_supported()) { 312 $format = strtolower($this->mdb->get_row_format($tablename)); 313 if ($format === 'compact' or $format === 'redundant') { 314 // Change the format before conversion so that we do not run out of space. 315 array_unshift($sqls, "ALTER TABLE {$this->prefix}$tablename ROW_FORMAT=Compressed"); 316 } 317 } 318 } 319 } 320 321 return $sqls; 322 } 323 324 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) 325 { 326 $tablename = $xmldb_table->getName(); 327 $dbcolumnsinfo = $this->mdb->get_columns($tablename); 328 329 if (($this->mdb->has_breaking_change_sqlmode()) && 330 ($dbcolumnsinfo[$xmldb_field->getName()]->meta_type == 'X') && 331 ($xmldb_field->getType() == XMLDB_TYPE_INTEGER)) { 332 // Ignore 1292 ER_TRUNCATED_WRONG_VALUE Truncated incorrect INTEGER value: '%s'. 333 $altercolumnsqlorig = $this->alter_column_sql; 334 $this->alter_column_sql = str_replace('ALTER TABLE', 'ALTER IGNORE TABLE', $this->alter_column_sql); 335 $result = parent::getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause); 336 // Restore the original ALTER SQL statement pattern. 337 $this->alter_column_sql = $altercolumnsqlorig; 338 339 return $result; 340 } 341 342 return parent::getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause); 343 } 344 345 /** 346 * Given one correct xmldb_table, returns the SQL statements 347 * to create temporary table (inside one array). 348 * 349 * @param xmldb_table $xmldb_table The xmldb_table object instance. 350 * @return array of sql statements 351 */ 352 public function getCreateTempTableSQL($xmldb_table) { 353 // Do we know collation? 354 $collation = $this->mdb->get_dbcollation(); 355 $this->temptables->add_temptable($xmldb_table->getName()); 356 357 $sqlarr = parent::getCreateTableSQL($xmldb_table); 358 359 // Let's inject the extra MySQL tweaks. 360 foreach ($sqlarr as $i=>$sql) { 361 if (strpos($sql, 'CREATE TABLE ') === 0) { 362 // We do not want the engine hack included in create table SQL. 363 $sqlarr[$i] = preg_replace('/^CREATE TABLE (.*)/s', 'CREATE TEMPORARY TABLE $1', $sql); 364 if ($collation) { 365 if (strpos($collation, 'utf8_') === 0) { 366 $sqlarr[$i] .= " DEFAULT CHARACTER SET utf8"; 367 } 368 $sqlarr[$i] .= " DEFAULT COLLATE $collation ROW_FORMAT=DYNAMIC"; 369 } 370 } 371 } 372 373 return $sqlarr; 374 } 375 376 /** 377 * Given one correct xmldb_table, returns the SQL statements 378 * to drop it (inside one array). 379 * 380 * @param xmldb_table $xmldb_table The table to drop. 381 * @return array SQL statement(s) for dropping the specified table. 382 */ 383 public function getDropTableSQL($xmldb_table) { 384 $sqlarr = parent::getDropTableSQL($xmldb_table); 385 if ($this->temptables->is_temptable($xmldb_table->getName())) { 386 $sqlarr = preg_replace('/^DROP TABLE/', "DROP TEMPORARY TABLE", $sqlarr); 387 } 388 return $sqlarr; 389 } 390 391 /** 392 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type. 393 * 394 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants. 395 * @param int $xmldb_length The length of that data type. 396 * @param int $xmldb_decimals The decimal places of precision of the data type. 397 * @return string The DB defined data type. 398 */ 399 public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) { 400 401 switch ($xmldb_type) { 402 case XMLDB_TYPE_INTEGER: // From http://mysql.com/doc/refman/5.0/en/numeric-types.html! 403 if (empty($xmldb_length)) { 404 $xmldb_length = 10; 405 } 406 if ($xmldb_length > 9) { 407 $dbtype = 'BIGINT'; 408 } else if ($xmldb_length > 6) { 409 $dbtype = 'INT'; 410 } else if ($xmldb_length > 4) { 411 $dbtype = 'MEDIUMINT'; 412 } else if ($xmldb_length > 2) { 413 $dbtype = 'SMALLINT'; 414 } else { 415 $dbtype = 'TINYINT'; 416 } 417 $dbtype .= '(' . $xmldb_length . ')'; 418 break; 419 case XMLDB_TYPE_NUMBER: 420 $dbtype = $this->number_type; 421 if (!empty($xmldb_length)) { 422 $dbtype .= '(' . $xmldb_length; 423 if (!empty($xmldb_decimals)) { 424 $dbtype .= ',' . $xmldb_decimals; 425 } 426 $dbtype .= ')'; 427 } 428 break; 429 case XMLDB_TYPE_FLOAT: 430 $dbtype = 'DOUBLE'; 431 if (!empty($xmldb_decimals)) { 432 if ($xmldb_decimals < 6) { 433 $dbtype = 'FLOAT'; 434 } 435 } 436 if (!empty($xmldb_length)) { 437 $dbtype .= '(' . $xmldb_length; 438 if (!empty($xmldb_decimals)) { 439 $dbtype .= ',' . $xmldb_decimals; 440 } else { 441 $dbtype .= ', 0'; // In MySQL, if length is specified, decimals are mandatory for FLOATs 442 } 443 $dbtype .= ')'; 444 } 445 break; 446 case XMLDB_TYPE_CHAR: 447 $dbtype = 'VARCHAR'; 448 if (empty($xmldb_length)) { 449 $xmldb_length='255'; 450 } 451 $dbtype .= '(' . $xmldb_length . ')'; 452 if ($collation = $this->mdb->get_dbcollation()) { 453 if (strpos($collation, 'utf8_') === 0) { 454 $dbtype .= " CHARACTER SET utf8"; 455 } 456 $dbtype .= " COLLATE $collation"; 457 } 458 break; 459 case XMLDB_TYPE_TEXT: 460 $dbtype = 'LONGTEXT'; 461 if ($collation = $this->mdb->get_dbcollation()) { 462 if (strpos($collation, 'utf8_') === 0) { 463 $dbtype .= " CHARACTER SET utf8"; 464 } 465 $dbtype .= " COLLATE $collation"; 466 } 467 break; 468 case XMLDB_TYPE_BINARY: 469 $dbtype = 'LONGBLOB'; 470 break; 471 case XMLDB_TYPE_DATETIME: 472 $dbtype = 'DATETIME'; 473 } 474 return $dbtype; 475 } 476 477 /** 478 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default 479 * (usually invoked from getModifyDefaultSQL() 480 * 481 * @param xmldb_table $xmldb_table The xmldb_table object instance. 482 * @param xmldb_field $xmldb_field The xmldb_field object instance. 483 * @return array Array of SQL statements to create a field's default. 484 */ 485 public function getCreateDefaultSQL($xmldb_table, $xmldb_field) { 486 // Just a wrapper over the getAlterFieldSQL() function for MySQL that 487 // is capable of handling defaults 488 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); 489 } 490 491 /** 492 * Given one correct xmldb_field and the new name, returns the SQL statements 493 * to rename it (inside one array). 494 * 495 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 496 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from. 497 * @param string $newname The new name to rename the field to. 498 * @return array The SQL statements for renaming the field. 499 */ 500 public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) { 501 // NOTE: MySQL is pretty different from the standard to justify this overloading. 502 503 // Need a clone of xmldb_field to perform the change leaving original unmodified 504 $xmldb_field_clone = clone($xmldb_field); 505 506 // Change the name of the field to perform the change 507 $xmldb_field_clone->setName($newname); 508 509 $fieldsql = $this->getFieldSQL($xmldb_table, $xmldb_field_clone); 510 511 $sql = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . ' CHANGE ' . 512 $this->getEncQuoted($xmldb_field->getName()) . ' ' . $fieldsql; 513 514 return array($sql); 515 } 516 517 /** 518 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default 519 * (usually invoked from getModifyDefaultSQL() 520 * 521 * Note that this method may be dropped in future. 522 * 523 * @param xmldb_table $xmldb_table The xmldb_table object instance. 524 * @param xmldb_field $xmldb_field The xmldb_field object instance. 525 * @return array Array of SQL statements to create a field's default. 526 * 527 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL() 528 */ 529 public function getDropDefaultSQL($xmldb_table, $xmldb_field) { 530 // Just a wrapper over the getAlterFieldSQL() function for MySQL that 531 // is capable of handling defaults 532 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); 533 } 534 535 /** 536 * Returns the code (array of statements) needed to add one comment to the table. 537 * 538 * @param xmldb_table $xmldb_table The xmldb_table object instance. 539 * @return array Array of SQL statements to add one comment to the table. 540 */ 541 function getCommentSQL ($xmldb_table) { 542 $comment = ''; 543 544 if ($xmldb_table->getComment()) { 545 $comment .= 'ALTER TABLE ' . $this->getTableName($xmldb_table); 546 $comment .= " COMMENT='" . $this->addslashes(substr($xmldb_table->getComment(), 0, 60)) . "'"; 547 } 548 return array($comment); 549 } 550 551 /** 552 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg). 553 * 554 * (MySQL requires the whole xmldb_table object to be specified, so we add it always) 555 * 556 * This is invoked from getNameForObject(). 557 * Only some DB have this implemented. 558 * 559 * @param string $object_name The object's name to check for. 560 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg). 561 * @param string $table_name The table's name to check in 562 * @return bool If such name is currently in use (true) or no (false) 563 */ 564 public function isNameInUse($object_name, $type, $table_name) { 565 566 switch($type) { 567 case 'ix': 568 case 'uix': 569 // First of all, check table exists 570 $metatables = $this->mdb->get_tables(); 571 if (isset($metatables[$table_name])) { 572 // Fetch all the indexes in the table 573 if ($indexes = $this->mdb->get_indexes($table_name)) { 574 // Look for existing index in array 575 if (isset($indexes[$object_name])) { 576 return true; 577 } 578 } 579 } 580 break; 581 } 582 return false; //No name in use found 583 } 584 585 586 /** 587 * Returns an array of reserved words (lowercase) for this DB 588 * @return array An array of database specific reserved words 589 */ 590 public static function getReservedWords() { 591 // This file contains the reserved words for MySQL databases. 592 $reserved_words = array ( 593 // From http://dev.mysql.com/doc/refman/6.0/en/reserved-words.html. 594 'accessible', 'add', 'all', 'alter', 'analyze', 'and', 'as', 'asc', 595 'asensitive', 'before', 'between', 'bigint', 'binary', 596 'blob', 'both', 'by', 'call', 'cascade', 'case', 'change', 597 'char', 'character', 'check', 'collate', 'column', 598 'condition', 'connection', 'constraint', 'continue', 599 'convert', 'create', 'cross', 'current_date', 'current_time', 600 'current_timestamp', 'current_user', 'cursor', 'database', 601 'databases', 'day_hour', 'day_microsecond', 602 'day_minute', 'day_second', 'dec', 'decimal', 'declare', 603 'default', 'delayed', 'delete', 'desc', 'describe', 604 'deterministic', 'distinct', 'distinctrow', 'div', 'double', 605 'drop', 'dual', 'each', 'else', 'elseif', 'enclosed', 'escaped', 606 'exists', 'exit', 'explain', 'false', 'fetch', 'float', 'float4', 607 'float8', 'for', 'force', 'foreign', 'from', 'fulltext', 'grant', 608 'group', 'having', 'high_priority', 'hour_microsecond', 609 'hour_minute', 'hour_second', 'if', 'ignore', 'in', 'index', 610 'infile', 'inner', 'inout', 'insensitive', 'insert', 'int', 'int1', 611 'int2', 'int3', 'int4', 'int8', 'integer', 'interval', 'into', 'is', 612 'iterate', 'join', 'key', 'keys', 'kill', 'leading', 'leave', 'left', 613 'like', 'limit', 'linear', 'lines', 'load', 'localtime', 'localtimestamp', 614 'lock', 'long', 'longblob', 'longtext', 'loop', 'low_priority', 'master_heartbeat_period', 615 'master_ssl_verify_server_cert', 'match', 'mediumblob', 'mediumint', 'mediumtext', 616 'middleint', 'minute_microsecond', 'minute_second', 617 'mod', 'modifies', 'natural', 'not', 'no_write_to_binlog', 618 'null', 'numeric', 'on', 'optimize', 'option', 'optionally', 619 'or', 'order', 'out', 'outer', 'outfile', 'overwrite', 'precision', 'primary', 620 'procedure', 'purge', 'raid0', 'range', 'read', 'read_only', 'read_write', 'reads', 'real', 621 'references', 'regexp', 'release', 'rename', 'repeat', 'replace', 622 'require', 'restrict', 'return', 'revoke', 'right', 'rlike', 'schema', 623 'schemas', 'second_microsecond', 'select', 'sensitive', 624 'separator', 'set', 'show', 'smallint', 'soname', 'spatial', 625 'specific', 'sql', 'sqlexception', 'sqlstate', 'sqlwarning', 626 'sql_big_result', 'sql_calc_found_rows', 'sql_small_result', 627 'ssl', 'starting', 'straight_join', 'table', 'terminated', 'then', 628 'tinyblob', 'tinyint', 'tinytext', 'to', 'trailing', 'trigger', 'true', 629 'undo', 'union', 'unique', 'unlock', 'unsigned', 'update', 630 'upgrade', 'usage', 'use', 'using', 'utc_date', 'utc_time', 631 'utc_timestamp', 'values', 'varbinary', 'varchar', 'varcharacter', 632 'varying', 'when', 'where', 'while', 'with', 'write', 'x509', 633 'xor', 'year_month', 'zerofill', 634 // Added in MySQL 8.0, compared to MySQL 5.7: 635 // https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-new-in-current-series. 636 '_filename', 'admin', 'cume_dist', 'dense_rank', 'empty', 'except', 'first_value', 'grouping', 'groups', 637 'json_table', 'lag', 'last_value', 'lead', 'nth_value', 'ntile', 638 'of', 'over', 'percent_rank', 'persist', 'persist_only', 'rank', 'recursive', 'row_number', 639 'system', 'window' 640 ); 641 return $reserved_words; 642 } 643} 644