What's the Difference Between User-Defined, Local, and System Variables in MySQL?

MySQL variable declaration and usage

In MySQL, in order to use variables in the second query, they need to be declared and initialized first.

User-defined variables (starting with @)

• No declaration required.
• Can be accessed directly without prior declaration or initialization.
• The value is NULL when not initialized, and the data type is string.
• Use SET or SELECT statement for initialization.
• Session specific (cannot be viewed or used by other clients).
• For example:

  <code class="language-sql">SET @start = 1, @finish = 10;
  SELECT * FROM places WHERE place BETWEEN @start AND @finish;</code>

Local variables (no prefix)

• Requires the DECLARE statement.
• is used as an input parameter or local variable in a stored procedure.
• For example:

  <code class="language-sql">DECLARE start INT unsigned DEFAULT 1;
  DECLARE finish INT unsigned DEFAULT 10;</code>

• If the DEFAULT clause is missing, the initial value is NULL.
• Scope is limited to the BEGIN...END block in which they are declared.

Server system variables (starting with @@)

• System variables managed by the MySQL server.
• Can be GLOBAL (global), SESSION (session) or BOTH (both).
• Affects server operations (GLOBAL) or individual client connections (SESSION).
• Use SHOW VARIABLES or SELECT @@var_name to view.
• Use SET GLOBAL or SET SESSION to modify dynamically.
• For example:

  <code class="language-sql">SET GLOBAL sort_buffer_size=1000000;</code>

The above is the detailed content of What's the Difference Between User-Defined, Local, and System Variables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!