CoreUI Logo CoreUI Logo
Questions in category: MySQL (MySQL)
返回
软件 >> MySQL
<[1] [2] [3] [4] [5] >

21. MySQL中的全局变量

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)

 

 

22. Incorrect datetime value: '0000-00-00 00:00:00'

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

23. [mysql] information_schema 数据库

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)

24. 查看 mysql 中的 process

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)

25. mysql下查询一些系统变量

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)

 

26. 列出 MySQL 中所有以 have 开头的变量

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)

27. 查看 MySQL 中的引擎

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)

 

28. mysql 中的 status 命令

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
--------------

 

29. MySQL 中修改某个用户的密码

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   |     |         |       |
+------------------------+-----------------------------------+------+-----+---------+-------+

 

30. 远程连接云上Linux服务器的MySQL

Posted by haifeng on 2020-10-01 17:48:22 last update 2020-10-01 18:05:38 | Answers (0) | 收藏

目标：我们希望本地电脑编写的 .exe 程序能够连接远程服务器上的MySQL. 其权限被严格限制在一定范围内.

首先使用 root 账号登陆 mysql, 创建一个专门用于远程登陆的账号. 这是从安全角度考. 另外, 如果这个 .exe 程序是用于分发的, 登陆账号的密码如果编译到 .exe 程序中, 则可能会被泄露. 

假设专用登陆的账号为 haifeng, 其密码是 password, 需访问或操作的数据库是 test. 则在mysql 下用root账号执行下面的语句.

GRANT ALL PRIVILEGES ON test.* TO 'haifeng'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 

第二步, 编辑 mysql 的配置文件 my.cnf . 在 ubuntu 14 系统中, 其位于 /etc/mysql/ 目录下. 新版本的可能位于 /etc/mysql/mysql.conf.d/ 目录下.

使用 vim 或其他编辑器打开此文件, 找到 bind-address 一行.  mysql 一般默认未开通远程访问, 因此有如下这一行

bind-address 127.0.0.1 

将其注释, 即前面加上 #

#bind-address 127.0.0.1

保存后退出. 并重启 mysql. 重启的命令是 /etc/init.d/mysqld restart

 

第三步, 编写 C 程序连接此远程服务器上的 mysql.

 

<[1] [2] [3] [4] [5] >