diff options
| -rw-r--r-- | BUILD/FINISH.sh | 2 | ||||
| -rwxr-xr-x | BUILD/compile-alpha-debug | 2 | ||||
| -rwxr-xr-x | BUILD/compile-ia64-debug-max | 2 | ||||
| -rw-r--r-- | Docs/manual.texi | 71 | ||||
| -rw-r--r-- | sql/sql_select.cc | 8 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 4 |
6 files changed, 75 insertions, 14 deletions
diff --git a/BUILD/FINISH.sh b/BUILD/FINISH.sh index 368ab339c2b..5e0ca925de1 100644 --- a/BUILD/FINISH.sh +++ b/BUILD/FINISH.sh @@ -10,7 +10,7 @@ done commands="\ $make -k clean || true -/bin/rm -f */.deps/*.P config.cache +/bin/rm -f */.deps/*.P config.cache innobase/config.cache bdb/build_unix/config.cache aclocal && autoheader && aclocal && automake && autoconf (cd bdb/dist && sh s_all) diff --git a/BUILD/compile-alpha-debug b/BUILD/compile-alpha-debug index 6672027445e..06b5a10675e 100755 --- a/BUILD/compile-alpha-debug +++ b/BUILD/compile-alpha-debug @@ -3,5 +3,5 @@ make -k clean /bin/rm -f config.cache aclocal; autoheader; aclocal; automake; autoconf -CFLAGS=-O6 CXX=gcc CXXFLAGS="-O6 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-debug +CFLAGS=-O6 CXX=gcc CXXFLAGS="-O6 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-debug --with-extra-charsets=complex make diff --git a/BUILD/compile-ia64-debug-max b/BUILD/compile-ia64-debug-max index 238156d0b63..9cd54de428d 100755 --- a/BUILD/compile-ia64-debug-max +++ b/BUILD/compile-ia64-debug-max @@ -1,5 +1,5 @@ gmake -k clean || true -/bin/rm -f */.deps/*.P config.cache +/bin/rm -f */.deps/*.P config.cache innobase/config.cache bdb/build_unix/config.cache aclocal && autoheader && aclocal && automake && autoconf (cd bdb/dist && sh s_all) diff --git a/Docs/manual.texi b/Docs/manual.texi index 344fb942dd1..04cfbdfabd9 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -3380,13 +3380,9 @@ of contributing to the MySQL development while getting something useful in return, is to purchase support directly from MySQL AB. @end itemize -For examples of situations when a commercial license is needed, please -visit the online manual at @uref{http://www.mysql.com/doc/}. - For buying commercial licenses and support, please visit the order section at @uref{https://order.mysql.com/}. - @node Using the MySQL server for free under GPL, , Using the MySQL server under a commercial license, MySQL server licenses @subsection Using the MySQL server for free under GPL @@ -14653,6 +14649,7 @@ mysql> SELECT * FROM shop; * example-Foreign keys:: Using foreign keys * Searching on two keys:: Searching on Two Keys * Calculating days:: Calculating visits per day +* example-AUTO_INCREMENT:: Using AUTO_INCREMENT @end menu @@ -14951,7 +14948,7 @@ DROP TABLE tmp; The above way to solve this query is in effect a @code{UNION} of two queries. -@node Calculating days, , Searching on two keys, Examples +@node Calculating days, example-AUTO_INCREMENT, Searching on two keys, Examples @subsection Calculating visits per day @findex BIT_OR @@ -14982,6 +14979,67 @@ The above calculates how many different days was used for a given year/month combination, with automatic removal of duplicate entries. +@node example-AUTO_INCREMENT, , Calculating days, Examples +@subsection Using AUTO_INCREMENT + +@cindex AUTO_INCREMENT +The @code{AUTO_INCREMENT} attribute can be used to generate an unique +identity for new rows: + +@example +CREATE TABLE animals (id mediumint not null auto_increment, +name char(30) not null, +primary key (id)); +INSERT INTO animals (name) values ("dog"),("cat"),("penguin"),("lax"),("whale"); +SELECT * FROM animals; + +Which returns: + ++----+---------+ +| id | name | ++----+---------+ +| 1 | dog | +| 2 | cat | +| 3 | penguin | +| 4 | lax | +| 5 | whale | ++----+---------+ +@end example + +For MyISAM and BDB tables you can specify @code{AUTO_INCREMENT} on +secondary column in a multi-column key. In this case the generated +value for the autoincrement column is calculated as +@code{MAX(auto_increment_column)+1) WHERE prefix=given-prefix}. This is +useful when you want to put data into ordered groups. + +@example +CREATE TABLE animals (grp enum ('fish','mammal','bird') not null, +id mediumint not null auto_increment, +name char(30) not null, +primary key (grp,id)); +INSERT INTO animals (grp,name) values ("mammal","dog"),("mammal","cat"),("bird","penguin"),("fish","lax"),("mammal","whale"); +SELECT * FROM animals order by grp,id; + +Which returns: + ++--------+----+---------+ +| grp | id | name | ++--------+----+---------+ +| fish | 1 | lax | +| mammal | 1 | dog | +| mammal | 2 | cat | +| mammal | 3 | whale | +| bird | 1 | penguin | ++--------+----+---------+ +@end example + +Note that in this case, the auto_increment value will be reused if you +delete the row with the biggest auto_increment value in any group. + +You can get the used @code{AUTO_INCREMENT} key with the +@code{LAST_INSERT_ID()} SQL function or the @code{mysql_insert_id()} API +function. + @node Batch mode, Twin, Examples, Tutorial @section Using @code{mysql} in Batch Mode @@ -35026,6 +35084,9 @@ positive number. This is done to avoid precision problems when numbers 'wrap' over from positive to negative and also to ensure that one doesn't accidentally get an auto_increment column that contains 0. +In MyISAM and BDB tables you can specify @code{AUTO_INCREMENT} secondary +column in a multi-column key. @xref{example-AUTO_INCREMENT}. + @cindex ODBC compatibility @cindex compatibility, with ODBC To make MySQL compatible with some ODBC applications, you can find diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 41c4ccd75b2..251ccae0299 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5084,15 +5084,15 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), static bool test_if_ref(Item_field *left_item,Item *right_item) { Field *field=left_item->field; - if (!field->table->const_table) // No need to change const test + // No need to change const test. We also have to keep tests on LEFT JOIN + if (!field->table->const_table && !field->table->maybe_null) { Item *ref_item=part_of_refkey(field->table,field); if (ref_item && ref_item->eq(right_item)) { if (right_item->type() == Item::FIELD_ITEM) - return (field->eq_def(((Item_field *) right_item)->field) && - !field->table->maybe_null); - if (right_item->const_item()) + return (field->eq_def(((Item_field *) right_item)->field)); + if (right_item->const_item() && !(right_item->is_null())) { // We can remove binary fields and numerical fields except float, // as float comparison isn't 100 % secure diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 5dae9eeb3cb..5096a737b8e 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1562,10 +1562,10 @@ simple_expr: | '{' ident expr '}' { $$= $3; } | MATCH ident_list_arg AGAINST '(' expr ')' { Select->ftfunc_list.push_back((Item_func_match *) - $$=new Item_func_match_nl(*$2,$5)); } + ($$=new Item_func_match_nl(*$2,$5))); } | MATCH ident_list_arg AGAINST '(' expr IN_SYM BOOLEAN_SYM MODE_SYM ')' { Select->ftfunc_list.push_back((Item_func_match *) - $$=new Item_func_match_bool(*$2,$5)); } + ($$=new Item_func_match_bool(*$2,$5))); } | BINARY expr %prec NEG { $$= new Item_func_binary($2); } | CASE_SYM opt_expr WHEN_SYM when_list opt_else END { $$= new Item_func_case(* $4, $2, $5 ) } |