MySQL Data Types It is MySQL is a very popular was first released in 1995 open-source relational database management system (RDBMS). MySQL is very fast, reliable, scalable, and easy to use. It is ideal for both small and large applications. compliant with the ANSI SQL standard and it is is developed, distributed, and supported by Oracle Corporation. Who Uses MySQL? • Huge websites like Facebook, Twitter, Airbnb, Booking.com, Uber, GitHub, YouTube, etc. • Content Management Systems like WordPress, Drupal, Joomla!, Contao, etc. • A very large number of web developers around the world . An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data. String Data Types Data type Description A FIXED length string (can contain letters, numbers, and special characters). CHAR(size) The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1 A VARIABLE length string (can contain letters, numbers, and special VARCHAR(size) characters). The size parameter specifies the maximum column length in characters - can be from 0 to 65535 Equal to CHAR(), but stores binary byte strings. The size parameter specifies BINARY(size) the column length in bytes. Default is 1 Equal to VARCHAR(), but stores binary byte strings. The size parameter VARBINARY(size) specifies the maximum column length in bytes. TINYBLOB For BLOBs (Binary Large OBjects). Max length: 255 bytes TINYTEXT Holds a string with a maximum length of 255 characters TEXT(size) Holds a string with a maximum length of 65,535 bytes BLOB(size) For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data A string object that can have only one value, chosen from a list of possible ENUM(val1, val2, values. You can list up to 65535 values in an ENUM list. If a value is inserted val3, ...) that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them SET(val1, val2, A string object that can have 0 or more values, chosen from a list of possible val3, ...) values. You can list up to 64 values in a SET list Numeric Data Types Data type Description A bit-value type. The number of bits per value is specified in size. The size BIT(size) parameter can hold a value from 1 to 64. The default value for size is 1. A very small integer. Signed range is from -128 to 127. Unsigned range is from TINYINT(size) 0 to 255. The size parameter specifies the maximum display width (which is 255) BOOL Zero is considered as false, nonzero values are considered as true. BOOLEAN Equal to BOOL A small integer. Signed range is from -32768 to 32767. Unsigned range is from SMALLINT(size) 0 to 65535. The size parameter specifies the maximum display width (which is 255) A medium integer. Signed range is from -8388608 to 8388607. Unsigned range MEDIUMINT(size is from 0 to 16777215. The size parameter specifies the maximum display ) width (which is 255) A medium integer. Signed range is from -2147483648 to 2147483647. INT(size) Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) INTEGER(size) Equal to INT(size) A large integer. Signed range is from -9223372036854775808 to BIGINT(size) 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255) A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This FLOAT(size, d) syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data FLOAT(p) type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE() A normal-size floating point number. The total number of digits is specified in DOUBLE(size, d) size. The number of digits after the decimal point is specified in the d parameter DOUBLE PRECISION(size, d) An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. DECIMAL(size, d) The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0. DEC(size, d) Equal to DECIMAL(size,d) Date and Time Data Types Data type Description A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to DATE '9999-12-31' A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported DATETIME(fsp range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT ) and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. TIMESTAMP(f The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' sp) UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition TIME(fsp) A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59' A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and YEAR 0000. MySQL 8.0 does not support year in two-digit format. MySQL String Functions Function ASCII CHAR_LENGTH CHARACTER_LENG TH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION Description Returns the ASCII value for the specific character Returns the length of a string (in characters) Returns the length of a string (in characters) Adds two or more expressions together Adds two or more expressions together with a separator Returns the index position of a value in a list of values Returns the position of a string within a list of strings Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places Inserts a string within a string at the specified position and for a certain number of characters Returns the position of the first occurrence of a string in another string Converts a string to lower-case Extracts a number of characters from a string (starting from left) Returns the length of a string (in bytes) Returns the position of the first occurrence of a substring in a string Converts a string to lower-case Left-pads a string with another string, to a certain length Removes leading spaces from a string Extracts a substring from a string (starting at any position) Returns the position of the first occurrence of a substring in a string Repeats a string as many times as specified Replaces all occurrences of a substring within a string, with a new REPLACE substring REVERSE Reverses a string and returns the result RIGHT Extracts a number of characters from a string (starting from right) RPAD Right-pads a string with another string, to a certain length RTRIM Removes trailing spaces from a string SPACE Returns a string of the specified number of space characters STRCMP Compares two strings SUBSTR Extracts a substring from a string (starting at any position) SUBSTRING Extracts a substring from a string (starting at any position) Returns a substring of a string before a specified number of delimiter SUBSTRING_INDEX occurs TRIM Removes leading and trailing spaces from a string UCASE Converts a string to upper-case UPPER Converts a string to upper-case REPEAT MySQL Numeric Functions Function ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 Description Returns the absolute value of a number Returns the arc cosine of a number Returns the arc sine of a number Returns the arc tangent of one or two numbers Returns the arc tangent of two numbers Returns the average value of an expression Returns the smallest integer value that is >= to a number Returns the smallest integer value that is >= to a number Returns the cosine of a number Returns the cotangent of a number Returns the number of records returned by a select query Converts a value in radians to degrees Used for integer division Returns e raised to the power of a specified number Returns the largest integer value that is <= to a number Returns the greatest value of the list of arguments Returns the smallest value of the list of arguments Returns the natural logarithm of a number Returns the natural logarithm of a number, or the logarithm of a number to a specified base Returns the natural logarithm of a number to base 10 Returns the natural logarithm of a number to base 2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Returns the maximum value in a set of values Returns the minimum value in a set of values Returns the remainder of a number divided by another number Returns the value of PI Returns the value of a number raised to the power of another number Returns the value of a number raised to the power of another number Converts a degree value into radians Returns a random number Rounds a number to a specified number of decimal places Returns the sign of a number Returns the sine of a number Returns the square root of a number Calculates the sum of a set of values Returns the tangent of a number Truncates a number to the specified number of decimal places MySQL Date Functions Function ADDDATE Description Adds a time/date interval to a date and then returns the date Adds a time interval to a time/datetime and then returns the ADDTIME time/datetime CURDATE Returns the current date CURRENT_DATE Returns the current date CURRENT_TIME Returns the current time CURRENT_TIMESTAMP Returns the current date and time CURTIME Returns the current time DATE Extracts the date part from a datetime expression DATEDIFF Returns the number of days between two date values DATE_ADD Adds a time/date interval to a date and then returns the date DATE_FORMAT Formats a date DATE_SUB Subtracts a time/date interval from a date and then returns the date DAY Returns the day of the month for a given date DAYNAME Returns the weekday name for a given date DAYOFMONTH Returns the day of the month for a given date DAYOFWEEK Returns the weekday index for a given date DAYOFYEAR Returns the day of the year for a given date EXTRACT Extracts a part from a given date FROM_DAYS Returns a date from a numeric datevalue HOUR Returns the hour part for a given date LAST_DAY Extracts the last day of the month for a given date LOCALTIME Returns the current date and time LOCALTIMESTAMP Returns the current date and time MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Creates and returns a date based on a year and a number of days value Creates and returns a time based on an hour, minute, and second value Returns the microsecond part of a time/datetime Returns the minute part of a time/datetime Returns the month part for a given date Returns the name of the month for a given date Returns the current date and time Adds a specified number of months to a period Returns the difference between two periods Returns the quarter of the year for a given date value Returns the seconds part of a time/datetime Returns a time value based on the specified seconds Returns a date based on a string and a format Subtracts a time/date interval from a date and then returns the date Subtracts a time interval from a datetime and then returns the time/datetime Returns the current date and time Extracts the time part from a given time/datetime Formats a time by a specified format Converts a time value into seconds Returns the difference between two time/datetime expressions Returns a datetime value based on a date or datetime value Returns the number of days between a date and date "0000-00-00" Returns the week number for a given date Returns the weekday number for a given date Returns the week number for a given date Returns the year part for a given date Returns the year and week number for a given date MySQL Advanced Functions Function Description Returns a binary representation of a number Converts a value to a binary string Goes through conditions and return a value when the first condition is met Converts a value (of any type) into a specified datatype Returns the first non-null value in a list BIN BINARY CASE CAST COALESCE CONNECTION_I Returns the unique connection ID for the current connection D CONV Converts a number from one numeric base system to another CONVERT Converts a value into the specified datatype or character set Returns the user name and host name for the MySQL account that the server CURRENT_USER used to authenticate the current client DATABASE Returns the name of the current database Returns a value if a condition is TRUE, or another value if a condition is FALSE Return a specified value if the expression is NULL, otherwise return the IFNULL expression ISNULL Returns 1 or 0 depending on whether an expression is NULL LAST_INSERT_I Returns the AUTO_INCREMENT id of the last row that has been inserted or D updated in a table Compares two expressions and returns NULL if they are equal. Otherwise, the NULLIF first expression is returned SESSION_USER Returns the current MySQL user name and host name SYSTEM_USER Returns the current MySQL user name and host name USER Returns the current MySQL user name and host name VERSION Returns the current version of the MySQL database IF Name: Description: ...