Posted by haifeng on 2020-11-10 11:04:46 last update 2020-11-10 11:06:01 | Answers (0) | 收藏
在数据库 information_schema 中, 查询所有会话变量
mysql> select COUNT(*) from SESSION_VARIABLES; # how many session variables
+-----------+
| COUNT( *) |
+-----------+
| 445 |
+-----------+
1 row in set, 1 warning (0.01 sec)
Posted by haifeng on 2020-11-10 10:52:54 last update 2020-11-10 11:02:37 | Answers (0) | 收藏
mysql> select @@global.sort_buffer_size;
+---------------------------+
| @@global.sort_buffer_size |
+---------------------------+
| 262144 |
+---------------------------+
1 row in set (0.00 sec)
事实上, mysql 的全局变量存储在数据库 information_schema 的表 GLOBAL_VARIABLES 中. 使用下面的语句
mysql> select * from information_schema.GLOBAL_VARIABLES;
将列出所有全局变量的信息.
对于 MySQL 5.6.17, 全局变量有431个. 上面的语句后面加上 Limit 100 可以列出前 100 条记录.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.17 |
+-----------+
1 row in set (0.00 sec)
Posted by haifeng on 2020-10-14 20:08:01 last update 2020-10-14 20:11:13 | Answers (0) | 收藏
服务器端的MySQL 是 5.5.54-0ubuntu0.12.04.1, 本地 MySQL 改用5.7 后, 某些 insert 语句由于对一些日期字段（datatime类型）默认插入'0000-00-00 00:00:00' 导致插入失败. 在 wampserver 下提示
Incorrect datetime value: '0000-00-00 00:00:00'
解决办法要么是修改出错的 insert 语句, 使得日期字段必须插入有效的日期值.
或者关闭 NO_ZERO_IN_DATE 模式. 这个存储在全局变量 @@global.sql_mode 中. 因此可以修改如下:
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.23 |
+-----------+
1 row in set (0.00 sec)
mysql> select @@global.sql_mode;
+-----------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+-----------------------------------------------------------------------------------------------+
| STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
References:
https://www.cnblogs.com/11120511cmd/p/12428030.html
Posted by haifeng on 2020-10-13 11:26:26 last update 2020-10-13 11:26:26 | Answers (0) | 收藏
information_schema 数据库中存储了许多动态的信息.
其中有一张表叫 COLUMNS.
执行 select * from information_schema.COLUMNS; 将输出很多行
它记录了所有数据库中的列的名称等属性
mysql> select * from COLUMNS;
...
1883 rows in set
--------------------------------------------
举个例子:
mysql> use information_schema;
Database changed
mysql> select * from COLUMNS where table_name='ships';
+---------------+-----------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT |
+---------------+-----------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+
| def | test_shipsinww2 | ships | name | 1 | NULL | YES | varchar | 20 | 40 | NULL | NULL | NULL | gbk | gbk_chinese_ci | varchar(20) | | | select,insert,update,references | |
| def | test_shipsinww2 | ships | class | 2 | NULL | YES | varchar | 20 | 40 | NULL | NULL | NULL | gbk | gbk_chinese_ci | varchar(20) | | | select,insert,update,references | |
| def | test_shipsinww2 | ships | launched | 3 | NULL | YES | year | NULL | NULL | NULL | NULL | NULL | NULL | NULL | year(4) | | | select,insert,update,references | |
+---------------+-----------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+
3 rows in set (0.04 sec)
Posted by haifeng on 2020-10-13 11:17:14 last update 2020-10-13 11:17:14 | Answers (0) | 收藏
mysql> use information_schema;
mysql> select * from processlist;
+-----+------+-----------------+--------------------+---------+------+-----------+---------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----+------+-----------------+--------------------+---------+------+-----------+---------------------------+
| 116 | root | localhost:56779 | information_schema | Query | 0 | executing | select * from processlist |
+-----+------+-----------------+--------------------+---------+------+-----------+---------------------------+
1 row in set (0.00 sec)
Posted by haifeng on 2020-10-13 11:08:51 last update 2020-10-13 11:10:00 | Answers (0) | 收藏
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
这是隔离层次, 目前是可重复读(repeatable-read)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
查询是否自动提交
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
Posted by haifeng on 2020-10-13 10:55:12 last update 2020-10-13 10:55:12 | Answers (0) | 收藏
mysql> show variables like 'have%';
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| have_compress | YES |
| have_crypt | NO |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_openssl | DISABLED |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_symlink | YES |
+----------------------+----------+
10 rows in set (0.04 sec)
Posted by haifeng on 2020-10-13 10:51:08 last update 2020-10-13 11:13:29 | Answers (0) | 收藏
使用 SHOW ENGINES; 命令查看 MySQL 中所使用的引擎.
show engines; 命令等价于使用
select * from information_schema.engines;
也就是说所列出的信息是存储在系统数据库 information_schema 中的engines 表中.
mysql> SHOW ENGINES\G;
*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)
Posted by haifeng on 2020-10-13 10:48:56 last update 2020-10-13 10:48:56 | Answers (0) | 收藏
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.6.17, for Win64 (x86_64)
Connection id: 114
Current database: mysql
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.17 MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 19 days 15 hours 54 min 58 sec
Threads: 1 Questions: 1956 Slow queries: 0 Opens: 586 Flush tables: 1 Open tables: 75 Queries per second avg: 0.001
--------------
Posted by haifeng on 2020-10-13 10:45:19 last update 2020-10-13 10:47:25 | Answers (0) | 收藏
以root用户为例. 特别的, 在安装 wampserver 后, root 用户的默认密码为空, 此时为了安全需要, 应修改其密码.
登录 mysql 后, 执行 SET PASSWORD 语句,
mysql> SET PASSWORD FOR root@localhost = password('theNewPassword');
Query OK, 0 rows affected (0.01 sec)
这里使用了 password() 函数对密码 theNewPassword 进行加密后存储.
可以到 mysql.user 表中进行查看
mysql> use mysql
Database changed
mysql> select host, user, password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *1A26DC91ED88BBF2AE0128BC32BBB93B8278924C |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
+-----------+------+-------------------------------------------+
4 rows in set (0.04 sec)
-------------------
查看user表中 Password 属性的类型, 发现是定长字符串 char(41)
mysql> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
+------------------------+-----------------------------------+------+-----+---------+-------+