[MySQL] 变量(参数)的查看跟设置
[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';
show global variables; show session/local variables;
@@GLOBAL.var_name @@SESSION.var_name 或 @@LOCAL.var_name
如果在变量名前没有级别限定符,将优先显示会话级的值。
最后一种查看变量值的方法是从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;
注意:和启动时不一样的是,在运行时设置的变量不允许使用后缀字母'K'、‘M'等,但可以用表达式来达到相同的效果,如:
SET GLOBAL read_buffer_size = 2*1024*1024
状态变量
状态变量可以使我们及时了解MySQL服务器的运行状况,可以使用show status语句查看。 状态变量和相同变量类似,也分为全局级和会话级,show status也支持like匹配查询,比较大的不同是状态变量只能由MySQL服务器本身设置和修改,对于用户来说是只读的,不可以通过SET语句设置和修改它们。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

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.

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.

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

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

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.

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.

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.
