How to declare a variable in MySQL?

There are mainly three types of variables in MySQL:

  1. User-defined variables (prefixed with @):You can access any user-defined variable without declaring it or initializing it. If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.SELECT @var_any_var_name You can initialize a variable using SET or SELECT statement:SET @start = 1, @finish = 10; orSELECT @start := 1, @finish := 10; SELECT * FROM places WHERE place BETWEEN @start AND @finish; User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.User-defined variables are session-specific. That is, a user variable defined by one client cannot be seen or used by other clients.They can be used in SELECT queries using Advanced MySQL user variable techniques.
  2. Local Variables (no prefix) :Local variables needs to be declared using DECLARE before accessing it.They can be used as local variables and the input parameters inside a stored procedure:DELIMITER // CREATE PROCEDURE sp_test(var1 INT) BEGIN DECLARE start INT unsigned DEFAULT 1; DECLARE finish INT unsigned DEFAULT 10; SELECT var1, start, finish; SELECT * FROM places WHERE place BETWEEN start AND finish; END; // DELIMITER ; CALL sp_test(5); If the DEFAULT clause is missing, the initial value is NULL.The scope of a local variable is the BEGIN ... END block within which it is declared.
  3. Server System Variables (prefixed with @@):The MySQL server maintains many system variables configured to a default value. They can be of type GLOBAL, SESSION or BOTH.Global variables affect the overall operation of the server whereas session variables affect its operation for individual client connections.To see the current values used by a running server, use the SHOW VARIABLES statement or SELECT @@var_name.SHOW VARIABLES LIKE '%wait_timeout%'; SELECT @@sort_buffer_size; They can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running using SET GLOBAL or SET SESSION:-- Syntax to Set value to a Global variable: SET GLOBAL sort_buffer_size=1000000; SET @@global.sort_buffer_size=1000000; -- Syntax to Set value to a Session variable: SET sort_buffer_size=1000000; SET SESSION sort_buffer_size=1000000; SET @@sort_buffer_size=1000000; SET @@local.sort_buffer_size=10000;

Related Posts:

  1. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  2. Unknown column in ‘field list’ error on MySQL Update query
  3. Unknown column in ‘field list’ error on MySQL Update query
  4. MySQL – Operand should contain 1 column(s)
  5. MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050
  6. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  7. MySQL create table if not exists and insert record only if table was created
  8. How do I use properly CASE..WHEN in MySQL
  9. How can I do a FULL OUTER JOIN in MySQL?
  10. MySQL query String contains
  11. What’s the difference between VARCHAR and CHAR?
  12. Case statement in MySQL
  13. MySQL Cannot Add Foreign Key Constraint
  14. How to split the name string in mysql?
  15. How do I import an SQL file using the command line in MySQL?
  16. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  17. mysql Foreign key constraint is incorrectly formed error
  18. MySQL: Invalid use of group function
  19. How can I return pivot table output in MySQL?
  20. Compare dates in MySQL
  21. MySQL “WITH” clause
  22. MySQL Multiple Joins in one query?
  23. Error Code: 2013. Lost connection to MySQL server during query
  24. MySQL syntax for Join Update
  25. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  26. how to drop partition without dropping data in MySQL?
  27. MySQL combine two columns into one column
  28. Simple way to calculate median with MySQL
  29. Error 1046 No database Selected, how to resolve?
  30. MySQL Error 1264: out of range value for column
  31. Remove duplicate rows in MySQL
  32. How to SUM and SUBTRACT using SQL?
  33. Cast from VARCHAR to INT – MySQL
  34. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  35. How do you force mysql LIKE to be case sensitive?
  36. MySQL – UPDATE multiple rows with different values in one query
  37. How to create a MySQL hierarchical recursive query?
  38. ERROR 1148: The used command is not allowed with this MySQL version
  39. Insert into a MySQL table or update if exists
  40. How to round down to nearest integer in MySQL?
  41. MySQL starts with searching issue
  42. Query to convert from datetime to date mysql
  43. Is it possible to GROUP BY multiple columns using MySQL?
  44. MySQL error: key specification without a key length
  45. Best way to test if a row exists in a MySQL table
  46. Check if MySQL table exists without using “select from” syntax?
  47. MySQL equivalent of DECODE function in Oracle
  48. mysql update column with value from another table
  49. The used SELECT statements have a different number of columns
  50. How to delete from multiple tables in MySQL?
  51. MySQL: Can’t create table (errno: 150)
  52. How to copy a row and insert in same table with a autoincrement field in MySQL?
  53. Join vs. sub-query
  54. Alternative to except in MySQL
  55. Equivalent of explode() to work with strings in MySQL
  56. ROW_NUMBER() in MySQ
  57. MySQL OPTIMIZE all tables?
  58. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  59. MySQL – Get row number on select
  60. MySQL error: Unknown column in ‘where clause’
  61. How to truncate the text returned for a column in a MySQL query
  62. Using union and order by clause in mysql
  63. MySQL LIKE IN()?
  64. SQL query to select dates between two dates
  65. Must declare the scalar variable
  66. Finding duplicate values in a SQL table
  67. How do I restore a dump file from mysqldump?
  68. Oracle error : ORA-00905: Missing keyword
  69. Difference between JOIN and INNER JOIN
  70. What is the difference between JOIN and UNION?
  71. “CASE” statement within “WHERE” clause in SQL Server 2008
  72. Column ‘user_id’ in field list is ambiguous
  73. ORA-00972 identifier is too long alias column name
  74. What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
  75. 1052: Column ‘id’ in field list is ambiguous
  76. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  77. SQL Server WITH statement
  78. SQL ZOO List each continent and the name of the country that comes first alphabetically
  79. Using group by on multiple columns
  80. Bulk load data conversion error (truncation)
  81. How to order by with union in SQL?
  82. PostgreSQL create table if not exists
  83. How To Write An Inner Join With WP Query
  84. Check if image exists before uploading with media_sideload_image()
  85. SQL query to get adjacent posts from search query
  86. How to import a Typo3 database to a wordpress site?
  87. Code to remove authors with no posts connected to them
  88. SQL Query in WordPress – Getting Impossible WHERE error
  89. Using AND and bracket grouping in SQL not working
  90. How can I convert everything from category X to have post format Link
  91. How to get database connection details without longing to cpanel in WordPress?
  92. SQL query to change custom field in WordPress database
  93. Reset post IDs with all post meta
  94. How to Add or Change Post Title
  95. Why does DROP TABLE-ing the `wp_options` reset my user session?
  96. How to refactor DB queries for better TTFB in WordPress?
  97. query sql-table and change entities
  98. Backtick (MySQL norm) added to SQL Server Query causing error
  99. SQL Query Search page
  100. Possible SQL injection. How to locate and fix?

Leave a Comment