[root@centos_6_8 ~]# sh mysql.shDatabaseinformation_schema
mysql
performance_schema
test
Field TypeNull Key DefaultExtra
idint(11) NO PRI NULLauto_increment
namechar(50) YES NULL
abs(-1)1bin(2)10
CEILING(1)1
FLOOR(2)2GREATEST(1,2,5,3)5LEAST(1,2,3,5,6)1LN(10)2.302585092994046
log(12)2.4849066497880004mod(10,5)0
pi()3.141593
rand()0.5353721264147872
round(1,10)1
sign(2)1
sqrt(4)2
truncate(123.23,1)123.2
ascii(12)49bit_length(123)24concat(1,2,3,4)1234concat_ws(0,1,2,3,4)1020304CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")
SQL-Tutorial-is-fun!
lcase("AVNA")
avnalower("AVNA")
avna
ucase("avna")
AVNAupper("avna")
AVNAleft("hello world",4)
hell
length("hello world")11
ltrim(" hello world")
hello world
POSITION("3" IN"W3Schools.com")2REPEAT("SQL Tutorial",3)
SQL TutorialSQL TutorialSQL TutorialREVERSE("str")
rtsRIGHT("str",2)
trRTRIM("str")strtrim("123 34")123 34CURDATE()2018-09-26
CURRENT_DATE()2018-09-26CURTIME()03:49:09
CURRENT_TIME()03:49:09DATE_FORMAT("2017-06-15", "%Y")2017HOUR("2017-06-20 09:34:00")9MINUTE("2017-06-20 09:34:00")34
MONTH("2017-06-20 09:34:00")6now()2018-09-26 03:49:09
year("2017-06-20 09:34:00")2017WEEK("2017-06-20 09:34:00")25MD5(123)
202cb962ac59075b964b07152d234b70
password(123)*23AE809DDACAF96AF0FD78ED04B6A265E05AA257
sha(123)
40bd001563085fc35165329ea1ff5c5ecbdbbeef
format("1231231",3)1,231,231.000inet_aton("192.168.13.14")3232238862inet_ntoa("3232238862")192.168.13.14
database()
test
benchmark(3,"12")0connection_id()37FOUND_ROWS()1
USER()
root@localhost
SYSTEM_USER()
root@localhostVERSION()5.5.48-logCharset DescriptionDefaultcollation Maxlen
big5 Big5 Traditional Chinese big5_chinese_ci2dec8DEC West European dec8_swedish_ci 1cp850 DOS West European cp850_general_ci1hp8 HP West European hp8_english_ci1koi8r KOI8-R Relcom Russian koi8r_general_ci 1latin1 cp1252 West European latin1_swedish_ci1latin2 ISO8859-2 Central European latin2_general_ci 1swe7 7bit Swedish swe7_swedish_ci 1
ascii US ASCII ascii_general_ci 1ujis EUC-JP Japanese ujis_japanese_ci 3sjis Shift-JIS Japanese sjis_japanese_ci 2hebrew ISO8859-8 Hebrew hebrew_general_ci 1tis620 TIS620 Thai tis620_thai_ci1euckr EUC-KR Korean euckr_korean_ci 2koi8u KOI8-U Ukrainian koi8u_general_ci 1gb2312 GB2312 Simplified Chinese gb2312_chinese_ci2greek ISO8859-7 Greek greek_general_ci 1cp1250 Windows Central European cp1250_general_ci1gbk GBK Simplified Chinese gbk_chinese_ci2latin5 ISO8859-9 Turkish latin5_turkish_ci 1armscii8 ARMSCII-8 Armenian armscii8_general_ci 1utf8 UTF-8 Unicode utf8_general_ci 3ucs2 UCS-2 Unicode ucs2_general_ci 2cp866 DOS Russian cp866_general_ci1keybcs2 DOS Kamenicky Czech-Slovak keybcs2_general_ci 1macce Mac Central European macce_general_ci1macroman Mac West European macroman_general_ci1cp852 DOS Central European cp852_general_ci1latin7 ISO8859-13 Baltic latin7_general_ci 1utf8mb4 UTF-8 Unicode utf8mb4_general_ci 4cp1251 Windows Cyrillic cp1251_general_ci1utf16 UTF-16 Unicode utf16_general_ci 4cp1256 Windows Arabic cp1256_general_ci1cp1257 Windows Baltic cp1257_general_ci1utf32 UTF-32 Unicode utf32_general_ci 4
binary Binary pseudo charset binary 1geostd8 GEOSTD8 Georgian geostd8_general_ci1cp932 SJISfor Windows Japanese cp932_japanese_ci 2eucjpms UJISfor Windows Japanese eucjpms_japanese_ci 3Collation Charset IdDefaultCompiled Sortlen
big5_chinese_ci big51 Yes Yes 1big5_bin big584 Yes 1dec8_swedish_ci dec83 Yes Yes 1dec8_bin dec869 Yes 1cp850_general_ci cp8504 Yes Yes 1cp850_bin cp85080 Yes 1hp8_english_ci hp86 Yes Yes 1hp8_bin hp872 Yes 1koi8r_general_ci koi8r7 Yes Yes 1koi8r_bin koi8r74 Yes 1latin1_german1_ci latin15 Yes 1latin1_swedish_ci latin18 Yes Yes 1latin1_danish_ci latin115 Yes 1latin1_german2_ci latin131 Yes 2latin1_bin latin147 Yes 1latin1_general_ci latin148 Yes 1latin1_general_cs latin149 Yes 1latin1_spanish_ci latin194 Yes 1latin2_czech_cs latin22 Yes 4latin2_general_ci latin29 Yes Yes 1latin2_hungarian_ci latin221 Yes 1latin2_croatian_ci latin227 Yes 1latin2_bin latin277 Yes 1swe7_swedish_ci swe710 Yes Yes 1swe7_bin swe782 Yes 1ascii_general_ciascii 11 Yes Yes 1ascii_binascii 65 Yes 1ujis_japanese_ci ujis12 Yes Yes 1ujis_bin ujis91 Yes 1sjis_japanese_ci sjis13 Yes Yes 1sjis_bin sjis88 Yes 1hebrew_general_ci hebrew16 Yes Yes 1hebrew_bin hebrew71 Yes 1tis620_thai_ci tis62018 Yes Yes 4tis620_bin tis62089 Yes 1euckr_korean_ci euckr19 Yes Yes 1euckr_bin euckr85 Yes 1koi8u_general_ci koi8u22 Yes Yes 1koi8u_bin koi8u75 Yes 1gb2312_chinese_ci gb231224 Yes Yes 1gb2312_bin gb231286 Yes 1greek_general_ci greek25 Yes Yes 1greek_bin greek70 Yes 1cp1250_general_ci cp125026 Yes Yes 1cp1250_czech_cs cp125034 Yes 2cp1250_croatian_ci cp125044 Yes 1cp1250_bin cp125066 Yes 1cp1250_polish_ci cp125099 Yes 1gbk_chinese_ci gbk28 Yes Yes 1gbk_bin gbk87 Yes 1latin5_turkish_ci latin530 Yes Yes 1latin5_bin latin578 Yes 1armscii8_general_ci armscii832 Yes Yes 1armscii8_bin armscii864 Yes 1utf8_general_ci utf833 Yes Yes 1utf8_bin utf883 Yes 1utf8_unicode_ci utf8192 Yes 8utf8_icelandic_ci utf8193 Yes 8utf8_latvian_ci utf8194 Yes 8utf8_romanian_ci utf8195 Yes 8utf8_slovenian_ci utf8196 Yes 8utf8_polish_ci utf8197 Yes 8utf8_estonian_ci utf8198 Yes 8utf8_spanish_ci utf8199 Yes 8utf8_swedish_ci utf8200 Yes 8utf8_turkish_ci utf8201 Yes 8utf8_czech_ci utf8202 Yes 8utf8_danish_ci utf8203 Yes 8utf8_lithuanian_ci utf8204 Yes 8utf8_slovak_ci utf8205 Yes 8utf8_spanish2_ci utf8206 Yes 8utf8_roman_ci utf8207 Yes 8utf8_persian_ci utf8208 Yes 8utf8_esperanto_ci utf8209 Yes 8utf8_hungarian_ci utf8210 Yes 8utf8_sinhala_ci utf8211 Yes 8utf8_general_mysql500_ci utf8223 Yes 1ucs2_general_ci ucs235 Yes Yes 1ucs2_bin ucs290 Yes 1ucs2_unicode_ci ucs2128 Yes 8ucs2_icelandic_ci ucs2129 Yes 8ucs2_latvian_ci ucs2130 Yes 8ucs2_romanian_ci ucs2131 Yes 8ucs2_slovenian_ci ucs2132 Yes 8ucs2_polish_ci ucs2133 Yes 8ucs2_estonian_ci ucs2134 Yes 8ucs2_spanish_ci ucs2135 Yes 8ucs2_swedish_ci ucs2136 Yes 8ucs2_turkish_ci ucs2137 Yes 8ucs2_czech_ci ucs2138 Yes 8ucs2_danish_ci ucs2139 Yes 8ucs2_lithuanian_ci ucs2140 Yes 8ucs2_slovak_ci ucs2141 Yes 8ucs2_spanish2_ci ucs2142 Yes 8ucs2_roman_ci ucs2143 Yes 8ucs2_persian_ci ucs2144 Yes 8ucs2_esperanto_ci ucs2145 Yes 8ucs2_hungarian_ci ucs2146 Yes 8ucs2_sinhala_ci ucs2147 Yes 8ucs2_general_mysql500_ci ucs2159 Yes 1cp866_general_ci cp86636 Yes Yes 1cp866_bin cp86668 Yes 1keybcs2_general_ci keybcs237 Yes Yes 1keybcs2_bin keybcs273 Yes 1macce_general_ci macce38 Yes Yes 1macce_bin macce43 Yes 1macroman_general_ci macroman39 Yes Yes 1macroman_bin macroman53 Yes 1cp852_general_ci cp85240 Yes Yes 1cp852_bin cp85281 Yes 1latin7_estonian_cs latin720 Yes 1latin7_general_ci latin741 Yes Yes 1latin7_general_cs latin742 Yes 1latin7_bin latin779 Yes 1utf8mb4_general_ci utf8mb445 Yes Yes 1utf8mb4_bin utf8mb446 Yes 1utf8mb4_unicode_ci utf8mb4224 Yes 8utf8mb4_icelandic_ci utf8mb4225 Yes 8utf8mb4_latvian_ci utf8mb4226 Yes 8utf8mb4_romanian_ci utf8mb4227 Yes 8utf8mb4_slovenian_ci utf8mb4228 Yes 8utf8mb4_polish_ci utf8mb4229 Yes 8utf8mb4_estonian_ci utf8mb4230 Yes 8utf8mb4_spanish_ci utf8mb4231 Yes 8utf8mb4_swedish_ci utf8mb4232 Yes 8utf8mb4_turkish_ci utf8mb4233 Yes 8utf8mb4_czech_ci utf8mb4234 Yes 8utf8mb4_danish_ci utf8mb4235 Yes 8utf8mb4_lithuanian_ci utf8mb4236 Yes 8utf8mb4_slovak_ci utf8mb4237 Yes 8utf8mb4_spanish2_ci utf8mb4238 Yes 8utf8mb4_roman_ci utf8mb4239 Yes 8utf8mb4_persian_ci utf8mb4240 Yes 8utf8mb4_esperanto_ci utf8mb4241 Yes 8utf8mb4_hungarian_ci utf8mb4242 Yes 8utf8mb4_sinhala_ci utf8mb4243 Yes 8cp1251_bulgarian_ci cp125114 Yes 1cp1251_ukrainian_ci cp125123 Yes 1cp1251_bin cp125150 Yes 1cp1251_general_ci cp125151 Yes Yes 1cp1251_general_cs cp125152 Yes 1utf16_general_ci utf1654 Yes Yes 1utf16_bin utf1655 Yes 1utf16_unicode_ci utf16101 Yes 8utf16_icelandic_ci utf16102 Yes 8utf16_latvian_ci utf16103 Yes 8utf16_romanian_ci utf16104 Yes 8utf16_slovenian_ci utf16105 Yes 8utf16_polish_ci utf16106 Yes 8utf16_estonian_ci utf16107 Yes 8utf16_spanish_ci utf16108 Yes 8utf16_swedish_ci utf16109 Yes 8utf16_turkish_ci utf16110 Yes 8utf16_czech_ci utf16111 Yes 8utf16_danish_ci utf16112 Yes 8utf16_lithuanian_ci utf16113 Yes 8utf16_slovak_ci utf16114 Yes 8utf16_spanish2_ci utf16115 Yes 8utf16_roman_ci utf16116 Yes 8utf16_persian_ci utf16117 Yes 8utf16_esperanto_ci utf16118 Yes 8utf16_hungarian_ci utf16119 Yes 8utf16_sinhala_ci utf16120 Yes 8cp1256_general_ci cp125657 Yes Yes 1cp1256_bin cp125667 Yes 1cp1257_lithuanian_ci cp125729 Yes 1cp1257_bin cp125758 Yes 1cp1257_general_ci cp125759 Yes Yes 1utf32_general_ci utf3260 Yes Yes 1utf32_bin utf3261 Yes 1utf32_unicode_ci utf32160 Yes 8utf32_icelandic_ci utf32161 Yes 8utf32_latvian_ci utf32162 Yes 8utf32_romanian_ci utf32163 Yes 8utf32_slovenian_ci utf32164 Yes 8utf32_polish_ci utf32165 Yes 8utf32_estonian_ci utf32166 Yes 8utf32_spanish_ci utf32167 Yes 8utf32_swedish_ci utf32168 Yes 8utf32_turkish_ci utf32169 Yes 8utf32_czech_ci utf32170 Yes 8utf32_danish_ci utf32171 Yes 8utf32_lithuanian_ci utf32172 Yes 8utf32_slovak_ci utf32173 Yes 8utf32_spanish2_ci utf32174 Yes 8utf32_roman_ci utf32175 Yes 8utf32_persian_ci utf32176 Yes 8utf32_esperanto_ci utf32177 Yes 8utf32_hungarian_ci utf32178 Yes 8utf32_sinhala_ci utf32179 Yes 8
binary binary 63 Yes Yes 1geostd8_general_ci geostd892 Yes Yes 1geostd8_bin geostd893 Yes 1cp932_japanese_ci cp93295 Yes Yes 1cp932_bin cp93296 Yes 1eucjpms_japanese_ci eucjpms97 Yes Yes 1eucjpms_bin eucjpms98 Yes 1Field TypeNull Key DefaultExtra
idint(11) NO 0namechar(50) YES NULL
Database Create DatabasetestCREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1*/
Databaseinformation_schema
mysql
performance_schema
test
Engine Support Comment Transactions XA Savepoints
MRG_MYISAM YES Collectionofidentical MyISAM tables NO NO NO
PERFORMANCE_SCHEMA YES PerformanceSchemaNO NO NO
CSV YES CSV storage engine NO NO NO
InnoDBDEFAULT Supports transactions, row-level locking, and foreignkeys YES YES YES
MyISAM YES MyISAM storage engine NO NO NO
MEMORY YES Hash based, storedin memory, useful for temporarytables NO NO NO
Tables_in_test
t1
t2
t3
Variable_name Value
auto_increment_increment1auto_increment_offset1autocommitONautomatic_sp_privilegesONback_log50basedir/usr/local/mysql
big_tablesOFFbinlog_cache_size32768binlog_direct_non_transactional_updatesOFFbinlog_format MIXED
binlog_stmt_cache_size32768bulk_insert_buffer_size8388608character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystembinarycharacter_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir/usr/local/mysql/share/charsets/collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server utf8_general_ci
completion_type NO_CHAIN
concurrent_insert AUTO
connect_timeout10datadir/usr/local/mysql/data/date_format%Y-%m-%d
datetime_format%Y-%m-%d %H:%i:%s
default_storage_engine InnoDB
default_week_format0delay_key_writeONdelayed_insert_limit100delayed_insert_timeout300delayed_queue_size1000div_precision_increment4engine_condition_pushdownONerror_count0event_schedulerOFFexpire_logs_days0external_user
flushOFFflush_time0foreign_key_checksONft_boolean_syntax+ ->
general_logOFFgeneral_log_file/usr/local/mysql/data/centos_6_8.loggroup_concat_max_len1024have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_geometry YES
have_innodb YES
have_ndbcluster NO
have_openssl NO
have_partitioning YES
have_profiling YES
have_query_cache YES
have_rtree_keys YES
have_ssl NO
have_symlink YES
hostname centos_6_8identity 0ignore_builtin_innodbOFFinit_connect
init_file
init_slave
innodb_adaptive_flushingONinnodb_adaptive_hash_indexONinnodb_additional_mem_pool_size8388608innodb_autoextend_increment8innodb_autoinc_lock_mode1innodb_buffer_pool_instances1innodb_buffer_pool_size134217728innodb_change_bufferingallinnodb_checksumsONinnodb_commit_concurrency0innodb_concurrency_tickets500innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewriteONinnodb_fast_shutdown1innodb_file_format Antelope
innodb_file_format_checkONinnodb_file_format_max Antelope
innodb_file_per_tableOFFinnodb_flush_log_at_trx_commit1innodb_flush_method
innodb_force_load_corruptedOFFinnodb_force_recovery0innodb_io_capacity200innodb_large_prefixOFFinnodb_lock_wait_timeout50innodb_locks_unsafe_for_binlogOFFinnodb_log_buffer_size8388608innodb_log_file_size5242880innodb_log_files_in_group2innodb_log_group_home_dir ./innodb_max_dirty_pages_pct75innodb_max_purge_lag0innodb_mirrored_log_groups1innodb_old_blocks_pct37innodb_old_blocks_time0innodb_open_files300innodb_print_all_deadlocksOFFinnodb_purge_batch_size20innodb_purge_threads0innodb_random_read_aheadOFFinnodb_read_ahead_threshold56innodb_read_io_threads4innodb_replication_delay0innodb_rollback_on_timeoutOFFinnodb_rollback_segments128innodb_spin_wait_delay6innodb_stats_method nulls_equal
innodb_stats_on_metadataONinnodb_stats_sample_pages8innodb_strict_modeOFFinnodb_support_xaONinnodb_sync_spin_loops30innodb_table_locksONinnodb_thread_concurrency0innodb_thread_sleep_delay10000innodb_use_native_aioOFFinnodb_use_sys_mallocONinnodb_version5.5.48innodb_write_io_threads4insert_id0interactive_timeout28800join_buffer_size131072keep_files_on_createOFFkey_buffer_size16777216key_cache_age_threshold300key_cache_block_size1024key_cache_division_limit100large_files_supportONlarge_page_size0large_pagesOFFlast_insert_id0lc_messages en_US
lc_messages_dir/usr/local/mysql/share/lc_time_names en_US
license GPL
local_infileONlock_wait_timeout31536000locked_in_memoryOFF
log OFFlog_binONlog_bin_trust_function_creatorsOFFlog_error/usr/local/mysql/data/centos_6_8.err
log_outputFILElog_queries_not_using_indexesOFFlog_slave_updatesOFFlog_slow_queriesOFFlog_warnings1long_query_time10.000000low_priority_updatesOFFlower_case_file_systemOFFlower_case_table_names0max_allowed_packet1048576max_binlog_cache_size18446744073709547520max_binlog_size1073741824max_binlog_stmt_cache_size18446744073709547520max_connect_errors10max_connections151max_delayed_threads20max_error_count64max_heap_table_size16777216max_insert_delayed_threads20max_join_size18446744073709551615max_length_for_sort_data1024max_long_data_size1048576max_prepared_stmt_count16382max_relay_log_size0max_seeks_for_key4294967295max_sort_length1024max_sp_recursion_depth0max_tmp_tables32max_user_connections0max_write_lock_count4294967295metadata_locks_cache_size1024min_examined_row_limit0multi_range_count256myisam_data_pointer_size6myisam_max_sort_file_size2146435072myisam_mmap_size4294967295myisam_recover_optionsOFFmyisam_repair_threads1myisam_sort_buffer_size8388608myisam_stats_method nulls_unequal
myisam_use_mmapOFFnet_buffer_length8192net_read_timeout30net_retry_count10net_write_timeout60newOFFoldOFFold_alter_tableOFFold_passwordsOFFopen_files_limit1024optimizer_prune_level1optimizer_search_depth62optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=onperformance_schemaOFFperformance_schema_events_waits_history_long_size10000performance_schema_events_waits_history_size10performance_schema_max_cond_classes80performance_schema_max_cond_instances1000performance_schema_max_file_classes50performance_schema_max_file_handles32768performance_schema_max_file_instances10000performance_schema_max_mutex_classes200performance_schema_max_mutex_instances1000000performance_schema_max_rwlock_classes30performance_schema_max_rwlock_instances1000000performance_schema_max_table_handles100000performance_schema_max_table_instances50000performance_schema_max_thread_classes50performance_schema_max_thread_instances1000pid_file/usr/local/mysql/data/centos_6_8.pid
plugin_dir/usr/local/mysql/lib/plugin/port3306preload_buffer_size32768profilingOFFprofiling_history_size15protocol_version10proxy_user
pseudo_slave_modeOFFpseudo_thread_id37query_alloc_block_size8192query_cache_limit1048576query_cache_min_res_unit4096query_cache_size0query_cache_typeONquery_cache_wlock_invalidateOFFquery_prealloc_size8192rand_seed10rand_seed20range_alloc_block_size4096read_buffer_size262144read_onlyOFFread_rnd_buffer_size524288relay_log
relay_log_index
relay_log_info_file relay-log.info
relay_log_purgeONrelay_log_recoveryOFFrelay_log_space_limit0report_host
report_password
report_port3306report_user
rpl_recovery_rank0secure_authOFFsecure_file_priv
server_id1skip_external_lockingONskip_name_resolveOFFskip_networkingOFFskip_show_databaseOFFslave_compressed_protocolOFFslave_exec_mode STRICT
slave_load_tmpdir/tmp
slave_max_allowed_packet1073741824slave_net_timeout3600slave_skip_errorsOFFslave_transaction_retries10slave_type_conversions
slow_launch_time2slow_query_logOFFslow_query_log_file/usr/local/mysql/data/centos_6_8-slow.logsocket/tmp/mysql.sock
sort_buffer_size524288sql_auto_is_nullOFFsql_big_selectsONsql_big_tablesOFFsql_buffer_resultOFFsql_log_binONsql_log_offOFFsql_low_priority_updatesOFFsql_max_join_size18446744073709551615sql_mode
sql_notesONsql_quote_show_createONsql_safe_updatesOFFsql_select_limit18446744073709551615sql_slave_skip_counter0sql_warningsOFFssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine InnoDB
stored_program_cache256sync_binlog0sync_frmONsync_master_info0sync_relay_log0sync_relay_log_info0system_time_zone CST
table_definition_cache400table_open_cache64thread_cache_size0thread_concurrency10thread_handling one-thread-per-connection
thread_stack196608time_format%H:%i:%s
time_zone SYSTEM
timed_mutexesOFF
timestamp 1537904949tmp_table_size16777216tmpdir/tmp
transaction_alloc_block_size8192transaction_prealloc_size4096tx_isolationREPEATABLE-READunique_checksONupdatable_views_with_limit YES
version5.5.48-logversion_comment Source distribution
version_compile_machine i686
version_compile_os Linux
wait_timeout28800warning_count0id name2rose3mary
Tables_in_test
t1
t2
t3
v_t1
id name2rose3mary
now()2018-09-26 03:49:09
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed NullIndex_type Comment Index_comment
t20 PRIMARY 1 id A 3 NULL NULLBTREE
t20 uniqe_name 1 name A 3 NULL NULLYES BTREE
t21 index_name 1 name A 3 NULL NULL YES BTREE
转播
