3.5.1 SET语句

3.5.1.1 XA

set xa=value

value:
  0
 | off
 | false
 | 1
 | on
 | true

例：

set xa=1；

注意事项：XA设置不能在多变量设置语句中使用。

3.5.1.2 AUTOCOMMIT

set autocommit=value

value:
  0
 | off
 | false
 | 1
 | on
 | true

例：

set autocommit=1；

注意事项：AUTOCOMMIT设置不能在多变量设置语句中使用。

3.5.1.3 NAMES

SET NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT}

例：

set names utf8;

set names utf8 collate utf8_general_ci;

set names default;

3.5.1.4 CHARSET

SET {CHARACTER SET | CHARSET}
{'charset_name' | DEFAULT}

例：

set CHARACTER SET utf8;

3.5.1.5 COLLATION_CONNECTION/CHAESET_SET_X

SET COLLATION_CONNECTION='collation_name'

SET CHARSET_SET_CLIENT='charset_name'，

SET CHARSET_SET_RESULTS='charset_name'  其中，'charset_name' 可以为NULL。

SET CHARSET_SET_CONNECTION='charset_name'

例：

set collation_connection=utf8_general_ci;

set CHARSET_SET_CLIENT=utf8；

set CHARSET_SET_RESULTS=utf8;

set CHARSET_SET_CONNECTION=utf8;

3.5.1.6 TRANSACTION ACCESS MODE

SET SESSION { TX_READ_ONLY |  TRANSACTION_READ_ONLY}=value

value:
  0
 | off
 | false
 | 1
 | on
 | true

例：

set session @@tx_read_only=1;

注意：

在MySQL中，支持在事务设置只读，但是不影响当前事务，由于dble的事务模型不同，因此可能会影响当前事务。

3.5.1.7 TRANSACTION ISOLATION LEVEL

SET SESSION {TRANSACTION_ISOLATION | TX_ISOLATION}=level

level:

READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE

例：

SET SESSION TX_ISOLATION=READ-COMMITTED;

3.5.1.8 USER/SYSTEM VARIABLE

SET variable_assignment[, variable_assignment ] ...

variable_assignment:

  @user_var_name = expr

| SESSION system_var_name = expr

| system_var_name = expr

| @@system_var_name = expr

| @@session.system_var_name = expr

注意事项：

  1. 不能设置全局系统变量。
  2. 支持的系统变量为：
audit_log_current_session
audit_log_filter_id
auto_increment_increment
auto_increment_offset
autocommit
big_tables
binlog_direct_non_transactional_updates
binlog_error_action
binlog_format
binlog_row_image
binlog_rows_query_log_events
binlogging_impossible_mode
block_encryption_mode
bulk_insert_buffer_size
character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
collation_connection
collation_database
collation_server
completion_type
debug
debug_sync
default_storage_engine
default_tmp_storage_engine
default_week_format
disconnect_on_expired_password
div_precision_increment
end_markers_in_json
eq_range_index_dive_limit
error_count
explicit_defaults_for_timestamp
external_user
foreign_key_checks
group_concat_max_len
gtid_next
gtid_owned
identity
innodb_create_intrinsic
innodb_ft_user_stopword_table
innodb_lock_wait_timeout
innodb_optimize_point_storage
innodb_strict_mode
innodb_support_xa
innodb_table_locks
innodb_tmpdir
insert_id
interactive_timeout
join_buffer_size
keep_files_on_create
last_insert_id
lc_messages
lc_time_names
lock_wait_timeout
long_query_time
low_priority_updates
max_allowed_packet
max_delayed_threads
max_error_count
max_execution_time
max_heap_table_size
max_insert_delayed_threads
max_join_size
max_length_for_sort_data
max_seeks_for_key
max_sort_length
max_sp_recursion_depth
max_statement_time
max_user_connections
min_examined_row_limit
myisam_repair_threads
myisam_sort_buffer_size
myisam_stats_method
ndb-allow-copying-alter-table
ndb_autoincrement_prefetch_sz
ndb-blob-read-batch-bytes
ndb-blob-write-batch-bytes
ndb_deferred_constraints
ndb_force_send
ndb_fully_replicated
ndb_index_stat_enable
ndb_index_stat_option
ndb_join_pushdown
ndb_log_bin
ndb_log_bin
ndb_table_no_logging
ndb_table_temporary
ndb_use_copying_alter_table
ndb_use_exact_count
ndb_use_transactions
ndbinfo_max_bytes
ndbinfo_max_rows
ndbinfo_show_hidden
ndbinfo_table_prefix
net_buffer_length
net_read_timeout
net_retry_count
net_write_timeout
new
old_alter_table
old_passwords
optimizer_prune_level
optimizer_search_depth
optimizer_switch
optimizer_trace
optimizer_trace_features
optimizer_trace_limit
optimizer_trace_max_mem_size
optimizer_trace_offset
parser_max_mem_size
preload_buffer_size
profiling
profiling_history_size
proxy_user
pseudo_slave_mode
pseudo_thread_id
query_alloc_block_size
query_cache_type
query_cache_wlock_invalidate
query_prealloc_size
rand_seed1
rand_seed2
range_alloc_block_size
range_optimizer_max_mem_size
rbr_exec_mode
read_buffer_size
read_rnd_buffer_size
session_track_gtids
session_track_schema
session_track_state_change
session_track_system_variables
show_old_temporals
sort_buffer_size
sql_auto_is_null
sql_big_selects
sql_buffer_result
sql_log_bin
sql_log_off
sql_mode
sql_notes
sql_quote_show_create
sql_safe_updates
sql_select_limit
sql_warnings
storage_engine
thread_pool_high_priority_connection
thread_pool_prio_kickup_timer
time_zone
timestamp
tmp_table_size
transaction_alloc_block_size
transaction_allow_batching
transaction_prealloc_size
transaction_write_set_extraction
tx_isolation
tx_read_only
unique_checks
updatable_views_with_limit
version_tokens_session
version_tokens_session_number
wait_timeout
warning_count

例：

set @a=20；

SET SESSION sql_mode = 'TRADITIONAL';

SET sql_mode = 'TRADITIONAL';
  1. insert_id 在使用过程中可能会在另一个前端连接中被重置而导致主键冲突的问题。sql_auto_is_null 和 insert_id 是联合使用的，使用时也有此限制。详情参见issue：https://github.com/actiontech/dble/issues/1252.

3.5.1.9 TRACE

用于观察单条SQL的性能，打开此开关后，可以执行需要观察性能的查询语句, 然后执行 show trace 来观察最后结果。
可以用 select @@trace 观察当前的开启状态。 详情请见 单条SQL性能trace 

set trace=value

value:
  0
 | off
 | false
 | 1
 | on
 | true

例：

set trace=1；

results matching ""

    No results matching ""