Table of Contents
系统变量
查看系统变量的值
设置和修改系统变量的值
状态变量
Home Database Mysql Tutorial [MySQL] 变量(参数)的查看跟设置

[MySQL] 变量(参数)的查看跟设置

Jun 07, 2016 pm 04:14 PM
mysql parameter variable Check set up

[MySQL] 变量(参数)的查看和设置 类于Oracle的参数文件,MySQL的选项文件(如my.cnf)用于配置MySQL服务器,但和Oracle叫法不一样,在MySQL里, 官方叫变量(Varialbes),但其实叫参数也是可以的,只要明白这俩是同一个东西就可以了。 MySQL的变量分为以下两

[MySQL] 变量(参数)的查看和设置

类似于Oracle的参数文件,MySQL的选项文件(如my.cnf)用于配置MySQL服务器,但和Oracle叫法不一样,在MySQL里, 官方叫变量(Varialbes),但其实叫参数也是可以的,只要明白这俩是同一个东西就可以了。

MySQL的变量分为以下两种:

1)系统变量:配置MySQL服务器的运行环境,可以用show variables查看

2)状态变量:监控MySQL服务器的运行状态,可以用show status查看


系统变量

系统变量按其作用域的不同可以分为以下两种:

1)分为全局(GLOBAL)级:对整个MySQL服务器有效

2)会话(SESSION或LOCAL)级:只影响当前会话

有些变量同时拥有以上两个级别,MySQL将在建立连接时用全局级变量初始化会话级变量,但一旦连接建立之后,全局级变量的改变不会影响到会话级变量。

查看系统变量的值

可以通过show vairables语句查看系统变量的值:

mysql> show variables like 'log%';
mysql> show variables where Variable_name like 'log%' and value='ON';
Copy after login
注意:show variables优先显示会话级变量的值,如果这个值不存在,则显示全局级变量的值,当然你也可以加上GLOBAL或SESSION关键字区别:

show global variables;
show session/local variables;
Copy after login
在写一些存储过程时,可能需要引用系统变量的值,可以使用如下方法:

@@GLOBAL.var_name
@@SESSION.var_name 或
@@LOCAL.var_name
Copy after login

如果在变量名前没有级别限定符,将优先显示会话级的值。

最后一种查看变量值的方法是从INFORMATION_SCHEMA数据库里的GLOBAL_VARIABLES和SESSION_VARIABLES表获得。

设置和修改系统变量的值

在MySQL服务器启动时,有以下两种方法设置系统变量的值:

1)命令行参数,如:mysqld --max_connections=200

2)选项文件(my.cnf)

在MySQL服务器启动后,如果需要修改系统变量的值,可以通过SET语句:

SET GLOBAL var_name = value;
SET @@GLOBAL.var_name = value;
SET SESSION var_name = value;
SET @@SESSION.var_name = value;
Copy after login
如果在变量名前没有级别限定符,表示修改会话级变量。

注意:和启动时不一样的是,在运行时设置的变量不允许使用后缀字母'K'、‘M'等,但可以用表达式来达到相同的效果,如:

SET GLOBAL read_buffer_size = 2*1024*1024
Copy after login

状态变量

状态变量可以使我们及时了解MySQL服务器的运行状况,可以使用show status语句查看。 状态变量和相同变量类似,也分为全局级和会话级,show status也支持like匹配查询,比较大的不同是状态变量只能由MySQL服务器本身设置和修改,对于用户来说是只读的,不可以通过SET语句设置和修改它们。



Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values ​​to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

The difference between oracle database and mysql The difference between oracle database and mysql May 10, 2024 am 01:54 AM

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.

See all articles