1.
下面这个查询:
select TIME from sys_log as log,a as emptytable;
sys_log里有三条数据,但是a这个个里面一条数据都没有,
但是为什么上面那个查询的结果也是空的呢? 它怎么查的?为什么不把sys_log里面的那三条记录查出来 ??
2.
为什么select @@session.tmpdir的时候提示它是global变量。
但是show session variables的时候会有它呢?
下面这个查询:
select TIME from sys_log as log,a as emptytable;
sys_log里有三条数据,但是a这个个里面一条数据都没有,
但是为什么上面那个查询的结果也是空的呢? 它怎么查的?为什么不把sys_log里面的那三条记录查出来 ??
2.
为什么select @@session.tmpdir的时候提示它是global变量。
但是show session variables的时候会有它呢?
所以你 show session variables 的时候可以看到。但不能修改。
是,但如果SESSION有自己的变量,可以则以自己的变量为准。
9.4. 系统变量
9.4.1. 结构式系统变量
MySQL可以访问许多系统和连接变量。当服务器运行时许多变量可以动态更改。这样通常允许你修改服务器操作而不需要停止并重启服务器。mysqld服务器维护两种变量。全局变量影响服务器整体操作。会话变量影响具体客户端连接的操作。当服务器启动时,它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。服务器启动后,通过连接服务器并执行SET GLOBAL var_name语句,可以动态更改这些全局变量。要想更改全局变量,必须具有SUPER权限。服务器还为每个连接的客户端维护一系列会话变量。在连接时使用相应全局变量的当前值对客户端的会话变量进行初始化。对于动态会话变量,客户端可以通过SET SESSION var_name语句更改它们。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量。对于全局变量的更改可以被访问该全局变量的任何客户端看见。然而,它只影响更改后连接的客户的从该全局变量初始化的相应会话变量。不影响目前已经连接的客户端的会话变量(即使客户端执行SET GLOBAL语句也不影响)。
5.3.3.1. 动态系统变量
服务器将维护许多表示其配置的系统变量。所有变量均有默认值。可以在命令行中或选项文件设置选项在服务器启动时对它们进行设置。大多数可以在运行时使用SET语句来设置。mysqld服务器维护两种变量。全局变量影响服务器的全局操作。会话变量影响具体客户端连接相关操作。服务器启动时,将所有全局变量初始化为默认值。可以在选项文件或命令行中指定的选项来更改这些默认值。服务器启动后,通过连接服务器并执行SET GLOBAL var_name语句可以更改动态全局变量。要想更改全局变量,必须具有SUPER权限。服务器还为每个客户端连接维护会话变量。连接时使用相应全局变量的当前值对客户端会话变量进行初始化。客户可以通过SET SESSION var_name语句来更改动态会话变量。设置会话变量不需要特殊权限,但客户可以只更改自己的会话变量,而不更改其它客户的会话变量。任何访问全局变量的客户端都可以看见对全局变量的更改。但是,它只影响在更改后连接的从该全局变量初始化相应会话变量的客户端。它不会影响已经连接上的客户端的会话变量(甚至是执行SET GLOBAL语句的客户端)。
5.1.4. Server System Variables http://dev.mysql.com/doc/refman/5.1/en/server-session-variables.html
5.1.5. Session System Variables 5.1.3. Server System Variables
The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running by means of the SET statement, which enables you to modify operation of the server without having to stop and restart it. You can refer to system variable values in expressions. There are several ways to see the names and values of system variables: To see the values that a server will use based on its compiled-in defaults and any option files that it reads, use this command: mysqld --verbose --helpTo see the values that a server will use based on its compiled-in defaults, ignoring the settings in any option files, use this command: mysqld --no-defaults --verbose --helpTo see the current values used by a running server, use the SHOW VARIABLES statement. This section provides a description of each system variable. Variables with no version indicated are present in all MySQL 5.1 releases. For historical information concerning their implementation, please see http://www.mysql.com/products/enterprise//5.0/en/, and http://www.mysql.com/products/enterprise//4.1/en/. The following table lists all available system variables: Table 5.2. mysqld System Variable SummaryName Cmd-Line Option file System Var Var Scope Dynamic
auto_increment_increment Yes Yes Yes Both Yes
auto_increment_offset Yes Yes Yes Both Yes
autocommit Yes Session Yes
automatic_sp_privileges Yes Global Yes
back_log Yes Yes Yes Global No
basedir Yes Yes Yes Global No
big-tables Yes Yes Yes
.....
table_definition_cache Yes Yes Yes Global Yes
table_lock_wait_timeout Yes Yes Yes Global Yes
table_open_cache Yes Yes Yes Global Yes
table_type Yes Both Yes
thread_cache_size Yes Yes Yes Global Yes
thread_concurrency Yes Yes Yes Global No
thread_handling Yes Yes Yes Global No
thread_stack Yes Yes Yes Global No
time_format Yes Yes Yes Both Yes
time_zone Yes Yes Yes Both Yes
timed_mutexes Yes Yes Yes Global Yes
timestamp Yes Session Yes
tmp_table_size Yes Yes Yes Both Yes
tmpdir Yes Yes Yes Global No
transaction_alloc_block_size Yes Yes Yes Both Yes
transaction_allow_batching Yes Session Yes
transaction_prealloc_size Yes Yes Yes Both Yes
tx_isolation Yes Both Yes
unique_checks Yes Session Yes
updatable_views_with_limit Yes Yes Yes Both Yes
version Yes Yes Yes Global No
version_comment Yes Global No
version_compile_machine Yes Global No
version_compile_os Yes Global No
wait_timeout Yes Yes Yes Both Yes
warning_count Yes Session No
[a] This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.
This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.
For additional system variable information, see these sections:
[b]5.1.4. Session System Variables
Several system variables exist only as session variables. These cannot be set at server startup but can be assigned values at runtime using the SET statement (except for those that are read only). Most of them are not displayed by SHOW VARIABLES, but you can obtain their values using SELECT. This section describes the session system variables. For information about setting or displaying their values, see Section 5.1.5, “Using System Variables”. For example: mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+The lettercase of these variables does not matter. The following table lists the system variables that have only session scope: Table 5.3. mysqld Session System Variable SummaryName Cmd-Line Option file System Var Dynamic
autocommit Yes Yes
big-tables Yes Yes
- Variable: big_tables Yes Yes
error_count Yes No
foreign_key_checks Yes Yes
identity Yes Yes
insert_id Yes Yes
last_insert_id Yes Yes
ndb_table_no_logging Yes Yes
ndb_table_temporary Yes Yes
profiling Yes Yes
rand_seed1 Yes Yes
rand_seed2 Yes Yes
sql_auto_is_null Yes Yes
sql_big_selects Yes Yes
sql_big_tables Yes Yes
sql_buffer_result Yes Yes
sql_log_bin Yes Yes
sql_log_off Yes Yes
sql_log_update Yes Yes
sql_notes Yes Yes
sql_quote_show_create Yes Yes
sql_safe_updates Yes Yes
sql_warnings Yes Yes
timestamp Yes Yes
transaction_allow_batching Yes Yes
unique_checks Yes Yes
warning_count Yes No
能不能解释一下为什么MYSQL要那么设计啊? 为什么要那么查。。
好像跟你说了N次谢谢了,这次改thank u .