Variables are used for storing data or information during the execution of a program. It is a way of labeling data with an appropriate name that helps to understand the program more clearly by the reader. The main purpose of the variable is to store data in memory and can be used throughout the program.
MySQL can use variables in three different ways, which are given below:
User-Defined Variable
Local Variable
System Variable
User-Defined Variable
Sometimes, we want to pass values from one statement to another statement. The user-defined variable enables us to store a value in one statement and later can refer it to another statement. MySQL provides a SET and SELECT statement to declare and initialize a variable. The user-defined variable name starts with @ symbol.
The user-defined variables are not case-sensitive such as @name and @NAME; both are the same. A user-defined variable declares by one person cannot visible to another person. We can assign the user-defined variable into limited data types like integer, float, decimal, string, or NULL. The user-defined variable can be a maximum of 64 characters in length.
Syntax
The following syntax is used to declare a user-defined variable.
By using the SET statement
SET@var_name =value;
2. By using the SELECT statement
SELECT@var_name :=value;
Local Variable
It is a type of variable that is not prefixed by @ symbol. The local variable is a strongly typed variable. The scope of the local variable is in a stored program block in which it is declared. MySQL uses the DECLARE keyword to specify the local variable. The DECLARE statement also combines a DEFAULT clause to provide a default value to a variable. If you do not provide the DEFAULT clause, it will give the initial value NULL. It is mainly used in the stored procedure program.
Syntax
We can use the DECLARE statement with the following syntax:
Let us see the following example to use the local variable.
Example
mysql> DECLARE total_price Oct(8,2) DEFAULT 0.0;
System Variable
System variables are a special class to all program units, which contains predefined variables. MySQL contains various system variables that configure its operation, and each system variable contains a default value. We can change some system variables dynamically by using the SET statement at runtime. It enables us to modify the server operation without stop and restart it. The system variable can also be used in the expressions.
MySQL server gives a bunch of system variables such as GLOBAL, SESSION, or MIX types. We can see the GLOBAL variable throughout the lifecycle of the server, whereas the SESSION variable remains active for a particular session only.
We can see the names and values of the system variable by using the following ways:
To see the current values used by the running server, execute the following command.
Properly defining the fields in a table is important to the overall optimization of your database. You should use only the type and size of field you really need to use. For example, do not define a field 10 characters wide, if you know you are only going to use 2 characters. These type of fields (or columns) are also referred to as data types, after the type of data you will be storing in those fields.
MySQL uses many different data types broken into three categories −
Numeric
Date and Time
String Types.
Let us now discuss them in detail.
Numeric Data Types
MySQL uses all the standard ANSI SQL numeric data types, so if you’re coming to MySQL from a different database system, these definitions will look familiar to you.
The following list shows the common numeric data types and their descriptions −
INT − A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.
TINYINT − A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.
SMALLINT − A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits.
MEDIUMINT − A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.
BIGINT − A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.
FLOAT(M,D) − A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.
DOUBLE(M,D) − A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE.
DECIMAL(M,D) − An unpacked floating-point number that cannot be unsigned. In the unpacked decimals, each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.
Date and Time Types
The MySQL date and time datatypes are as follows −
DATE − A date in YYYY-MM-DD format, between 1000–01–01 and 9999–12–31. For example, December 30th, 1973 would be stored as 1973–12–30.
DATETIME − A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000–01–01 00:00:00 and 9999–12–31 23:59:59. For example, 3:30 in the afternoon on December 30th, 1973 would be stored as 1973–12–30 15:30:00.
TIMESTAMP − A timestamp between midnight, January 1st, 1970 and sometime in 2037. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).
TIME − Stores the time in a HH:MM:SS format.
YEAR(M) − Stores a year in a 2-digit or a 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be between 1970 to 2069 (70 to 69). If the length is specified as 4, then YEAR can be 1901 to 2155. The default length is 4.
String Types
Although the numeric and date types are fun, most data you’ll store will be in a string format. This list describes the common string datatypes in MySQL.
CHAR(M) − A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.
VARCHAR(M) − A variable-length string between 1 and 255 characters in length. For example, VARCHAR(25). You must define a length when creating a VARCHAR field.
BLOB or TEXT − A field with a maximum length of 65535 characters. BLOBs are “Binary Large Objects” and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data. The difference between the two is that the sorts and comparisons on the stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.
TINYBLOB or TINYTEXT − A BLOB or TEXT column with a maximum length of 255 characters. You do not specify a length with TINYBLOB or TINYTEXT.
MEDIUMBLOB or MEDIUMTEXT − A BLOB or TEXT column with a maximum length of 16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.
LONGBLOB or LONGTEXT − A BLOB or TEXT column with a maximum length of 4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.
ENUM − An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL). For example, if you wanted your field to contain “A” or “B” or “C”, you would define your ENUM as ENUM (‘A’, ‘B’, ‘C’) and only those values (or NULL) could ever populate that field.
Like other relational database system, MySQL database server also has its advantages and disadvantages. Some of these advantages have been explained below.
Reduced Total Cost of Ownership
MySql one of the most popular open source database management system that allows you to manage relational database. Since MySql open source, you can use MySql free and if you want, you can tailor its source code according to your requirement. Most of the companies prefer MySQL because they don’t have to pay anything for this excellent product.
Portability
MySQL is cross platform database server. It can run on different plateform like Linux,Solaris and Windows etc. It is good choice for those project that target multiple platforms particularly web application. In fact, MySQL is a part of the famous LAMP (Linux Apache MySQL PHP) server stack which is used worldwide for web application development. MySql support many plateform with different languages like C, C++, PHP, PERL, JAVA, Python etc.
Seamless Connectivity
There are various secure and seamless connection mechanisms are available in order to connect with MySQL server. These connections include named pipes, TCP/IP sockets and UNIX Sockets.
Rapid Development and Round-the-Clock Uptime
MySQL comes with the assurance of 24x7 uptime and offers a wide range of high-availability solutions, including specialized cluster servers and master/slave replication configurations. MySQL has a very large developer community which releases regular patches and updates for MySQL.
Data Security
MySQL is globally recognize the most secure and reliable database management system used in popular web applications including WordPress, Drupal, Joomla, Facebook and Twitter. Data protected via password and good thing about these passwords is that they are stored in encrypted form and can not break these complex encryption algorithms.
Disadvantages:
MySQL database server has its disadvantages. Some of these disadvantages have been explained below.
MySQL lower version (5.0 or less) doesn’t support ROLE, COMMIT and stored procedure.
MySQL does not support a very large database size as efficiently.
MySQL doesn’t handle transactions very efficiently and it is prone to data corruption.
MySQL doesn’t have a good developing and debugging tool compared to other databases.
The following are the most important features of MySQL:
Relational Database Management System (RDBMS):
MySQL is a relational database management system. This database language is based on the SQL queries to access and manage the records of the table.
Easy to use:
MySQL is easy to use. We have to get only the basic knowledge of SQL. We can build and interact with MySQL by using only a few simple SQL statements.
It is secure:
MySQL consists of a solid data security layer that protects sensitive data from intruders. Also, passwords are encrypted in MySQL.
Client/ Server Architecture:
MySQL follows the working of a client/server architecture. There is a database server (MySQL) and arbitrarily many clients (application programs), which communicate with the server; that is, they can query data, save changes, etc.
Free to download:
MySQL is free to use so that we can download it from MySQL official website without any cost.
It is scalable:
MySQL supports multi-threading that makes it easily scalable. It can handle almost any amount of data, up to as much as 50 million rows or more. The default file size limit is about 4 GB. However, we can increase this number to a theoretical limit of 8 TB of data.
Speed:
MySQL is considered one of the very fast database languages, backed by a large number of the benchmark test.
High Flexibility:
MySQL supports a large number of embedded applications, which makes MySQL very flexible.
Compatible on many operating systems:
MySQL is compatible to run on many operating systems, like Novell NetWare, Windows* Linux*, many varieties of UNIX* (such as Sun* Solaris*, AIX, and DEC* UNIX), OS/2, FreeBSD*, and others. MySQL also provides a facility that the clients can run on the same computer as the server or on another computer (communication via a local network or the Internet).
Allows roll-back:
MySQL allows transactions to be rolled back, commit, and crash recovery.
Memory efficiency:
Its efficiency is high because it has a very low memory leakage problem.
High Performance:
MySQL is faster, more reliable, and cheaper because of its unique storage engine architecture. It provides very high-performance results in comparison to other databases without losing an essential functionality of the software. It has fast loading utilities because of the different cache memory.
High Productivity:
MySQL uses Triggers, Stored procedures, and views that allow the developer to give higher productivity.
Platform Independent:
It can download, install, and execute on most of the available operating systems.
Partitioning:
This feature improves the performance and provides fast management of the large database.
GUI Support:
MySQL provides a unified visual database graphical user interface tool named “MySQL Workbench” to work with database architects, developers, and Database Administrators. MySQL Workbench provides SQL development, data modeling, data migration, and comprehensive administration tools for server configuration, user administration, backup, and many more. MySQL has a fully GUI supports from MySQL Server version 5.6 and higher.
Dual Password Support:
MySQL version 8.0 provides support for dual passwords: one is the current password, and another is a secondary password, which allows us to transition to the new password.
Unireg, which is the code base of MySQL, was started in 1981.
MySQL was founded in 1995 in Sweden.
In 2000, MySQL went open source, so it could be accessed and used by all.
In the year 2001, Marten Mickos was elected as the CEO of MySQL.
In the year 2002, MySQL launched its headquarters in USA, in addition to Sweden headquarters.
In 2003, MySQL entered into a partnership with SAP, and many features were developed in MySQL keeping SAP in mind.
In 2005, MySQL launched MySQL Network. Oracle purchased Innobase, which is the backend of MySQL’s InnoDB storage.
In 2008, MySQL was acquired by Sun Microsystems.
In 2009, Sun Microsystems and Oracle entered intoa definitive agreement under which Oracle acquired Sun Microsystems.
Let us now understand the history of MySQL:
The mSQL database system was designed to connect the data that is stored in tables using customized fast low level (ISAM) routines.
ISAM refers to indexed sequential access method, which is a file management system. It is a technique that helps access records in the tables sequentially, i.e in the same order in which the records were entered into the table, or randomly with the help of an index. Every index can be used to define a different order for the records in the table.
ISAM was originally developed by IBM before the development of VSAM (Virtual Storage Access Method) and relational databases.
When mSQL was tested to see its efficiency, it was observed that mSQL was not quick enough or flexible enough for the requirements in hand.
MySQL was initially created for personal usage from mSQL based on the low-level language ISAM.
MySQL has been named after co-founder Monty Widenius’s daughter- My. The logo, a dolphin is known as ‘Sakila’.
MySQL is becoming so popular because of these following reasons:
1. Scalability and Flexibility
The MySQL database server provides the ultimate in scalability, sporting the capacity to handle deeply embedded applications with a footprint of only 1MB to running massive data warehouses holding terabytes of information.
Platform flexibility is a stalwart feature of MySQL with all flavors of Linux, UNIX, and Windows being supported. And, of course, the open source nature of MySQL allows complete customization for those wanting to add unique requirements to the database server.
2. High Performance
A unique storage-engine architecture allows database professionals to configure the MySQL database server specifically for particular applications, with the end result being amazing performance results.
Whether the intended application is a high-speed transactional processing system or a high-volume web site that services a billion queries a day, MySQL can meet the most demanding performance expectations of any system.
With high-speed load utilities, distinctive memory caches, full text indexes, and other performance-enhancing mechanisms, MySQL offers all the right ammunition for today’s critical business systems.
3. High Availability
Rock-solid reliability and constant availability are hallmarks of MySQL, with customers relying on MySQL to guarantee around-the-clock uptime. MySQL offers a variety of high-availability options from high-speed master/slave replication configurations, to specialized Cluster servers offering instant failover, to third party vendors offering unique high-availability solutions for the MySQL database server.
4. Robust Transactional Support
MySQL offers one of the most powerful transactional database engines on the market. Features include complete ACID (atomic, consistent, isolated, durable) transaction support, unlimited row-level locking, distributed transaction capability, and multi-version transaction support where readers never block writers and vice-versa. Full data integrity is also assured through server-enforced referential integrity, specialized transaction isolation levels, and instant deadlock detection.
5. Web and Data Warehouse Strengths
MySQL is the de-facto standard for high-traffic web sites because of its high-performance query engine, tremendously fast data insert capability, and strong support for specialized web functions like fast full text searches. These same strengths also apply to data warehousing environments where MySQL scales up into the terabyte range for either single servers or scale-out architectures. Other features like main memory tables, B-tree and hash indexes, and compressed archive tables that reduce storage requirements by up to eighty-percent make MySQL a strong standout for both web and business intelligence applications.
6. Strong Data Protection
Because guarding the data assets of corporations is the number one job of database professionals, MySQL offers exceptional security features that ensure absolute data protection. In terms of database authentication, MySQL provides powerful mechanisms for ensuring only authorized users have entry to the database server, with the ability to block users down to the client machine level being possible. SSH and SSL support are also provided to ensure safe and secure connections.
A granular object privilege framework is present so that users only see the data they should, and powerful data encryption and decryption functions ensure that sensitive data is protected from unauthorized viewing. Finally, backup and recovery utilities provided through MySQL and third party software vendors allow for complete logical and physical backup as well as full and point-in-time recovery.
7. Comprehensive Application Development
One of the reasons MySQL is the world’s most popular open source database is that it provides comprehensive support for every application development need. Within the database, support can be found for stored procedures, triggers, functions, views, cursors, ANSI-standard SQL, and more. For embedded applications, plug-in libraries are available to embed MySQL database support into nearly any application.
MySQL also provides connectors and drivers (ODBC, JDBC, etc.) that allow all forms of applications to make use of MySQL as a preferred data management server. It doesn’t matter if it’s PHP, Perl, Java, Visual Basic, or .NET, MySQL offers application developers everything they need to be successful in building database-driven information systems.
8. Management Ease
MySQL offers exceptional quick-start capability with the average time from software download to installation completion being less than fifteen minutes. This rule holds true whether the platform is Microsoft Windows, Linux, Macintosh, or UNIX. Once installed, self-management features like automatic space expansion, auto-restart, and dynamic configuration changes take much of the burden off already overworked database administrators.
MySQL also provides a complete suite of graphical management and migration tools that allow a DBA to manage, troubleshoot, and control the operation of many MySQL servers from a single workstation. Many third party software vendor tools are also available for MySQL that handle tasks ranging from data design and ETL, to complete database administration, job management, and performance monitoring.
9. Open Source Freedom and 24 x 7 Support
Many corporations are hesitant to fully commit to open source software because they believe they can’t get the type of support or professional service safety nets they currently rely on with proprietary software to ensure the overall success of their key applications. The questions of indemnification come up often as well.
These worries can be put to rest with MySQL as complete around-the-clock support as well as indemnification is available through MySQL Enterprise. MySQL is not a typical open source project as all the software is owned and supported by Oracle, and because of this, a unique cost and support model are available that provides a unique combination of open source freedom and trusted software with support.
10. Lowest Total Cost of Ownership
By migrating current database-drive applications to MySQL, or using MySQL for new development projects, corporations are realizing cost savings that many times stretch into seven figures.
Accomplished through the use of the MySQL database server and scale-out architectures that utilize low-cost commodity hardware, corporations are finding that they can achieve amazing levels of scalability and performance, all at a cost that is far less than those offered by proprietary and scale-up software vendors.
In addition, the reliability and easy maintainability of MySQL means that database administrators don’t waste time troubleshooting performance or downtime issues, but instead can concentrate on making a positive impact on higher level tasks that involve the business side of data.
