• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/MariaDB/MariaDB – Data Types
Popular Search:Oracle, SQL Server, MongoDB

MariaDB – Data Types

165 views 0 June 2, 2020 admin

Good field definitions are essential for the optimization of your database. The ideal approach requires that you exclusively use a field of the type and size needed. For example, if you will only use a field, five-characters wide, do not define a field, 20-characters wide. Field (or column) types are also known as data types given the data types stored within the field.

MariaDB data types can be categorized as numeric, date and time, and string values.

Numeric Data Types

The numeric data types supported by MariaDB are as follows:

  • TINYINT – This data type represents small integers falling within the signed range of -128 to 127, and the unsigned range of 0 to 255.
  • BOOLEAN – This data type associates a value 0 with “false,” and a value 1 with “true.”
  • SMALLINT – This data type represents integers within the signed range of -32768 to 32768, and the unsigned range of 0 to 65535.
  • MEDIUMINT – This data type represents integers in the signed range of -8388608 to 8388607, and the unsigned range of 0 to 16777215.
  • INT(also INTEGER) – This data type represents an integer of normal size. When marked as unsigned, the range spans 0 to 4294967295. When signed (the default setting), the range spans -2147483648 to 2147483647. When a column is set to ZEROFILL( an unsigned state), all its values are prepended by zeros to place M digits in the INT value.
  • BIGINT – This data type represents integers within the signed range of 9223372036854775808 to 9223372036854775807, and the unsigned range of 0 to 18446744073709551615.
  • DECIMAL( also DEC, NUMERIC, FIXED) – This data type represents precise fixed point numbers, with M specifying its digits and D specifying the digits after the decimal. The M value does not add “-” or the decimal point. If D is set to 0, no decimal or fraction part appears and the value will be rounded to the nearest DECIMAL on INSERT. The maximum permitted digits is 65, and the maximum for decimals is 30. Default value for M on omission is 10, and 0 for D on omission.
  • FLOAT – This data type represents a small, floating-point number of the value 0 or a number within the following ranges:o -3.402823466E+38 to -1.175494351E-38 and 1.175494351E-38 to 3.402823466E+38
  • DOUBLE (also REAL and DOUBLE PRECISION)– This data type represents normal-size, floating-point numbers of the value 0 or within the following ranges:

-1.7976931348623157E+308 to -2.2250738585072014E-308 and 2.2250738585072014E-308 to 1.7976931348623157E+308

  • BIT – This data type represents bit fields with M specifying the number of bits per value. On omission of M, the default is 1. Bit values can be applied with “ b’[value]’ ” in which value represents bit value in 0s and 1s. Zero-padding occurs automatically from the left for full length; for example, “10” becomes “0010.”

Date and Time Data Types

The date and time data types supported by MariaDB are as follows:

  • DATE – This data type represents a date range of “1000-01-01” to “9999-12-31,” and uses the “YYYY-MM-DD” date format.
  • TIME – This data type represents a time range of “-838:59:59.999999” to “838:59:59.999999.”
  • DATETIME – This data type represents the range “1000-01-01 00:00:00.000000” to “9999-12-31 23:59:59.999999.” It uses the “YYYY-MM-DD HH:MM:SS” format.
  • TIMESTAMP – This data type represents a timestamp of the “YYYY-MM-DD HH:MM:DD” format. It mainly finds use in detailing the time of database modifications, e.g., insertion or update.
  • YEAR – This data type represents a year in 4-digit format. The four-digit format allows values in the range of 1901 to 2155, and 0000.

String Data Types

The string type values supported by MariaDB are as follows:

  • String literals – This data type represents character sequences enclosed by quotes.
  • CHAR – This data type represents a right-padded, fixed-length string containing spaces of specified length. M represents column length of characters in a range of 0 to 255, its default value is 1.
  • VARCHAR – This data type represents a variable-length string, with an M range (maximum column length) of 0 to 65535.
  • BINARY – This data type represents binary byte strings, with M as the column length in bytes.
  • VARBINARY – This data type represents binary byte strings of variable length, with M as column length.
  • TINYBLOB – This data type represents a blob column with a maximum length of 255 (28 – 1) bytes. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.
  • BLOB – This data type represents a blob column with a maximum length of 65,535 (216 – 1) bytes. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.
  • MEDIUMBLOB – This data type represents a blob column with a maximum length of 16,777,215 (224 – 1) bytes. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.
  • LONGBLOB – This data type represents a blob column with a maximum length of 4,294,967,295(232 – 1) bytes. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.
  • TINYTEXT – This data type represents a text column with a maximum length of 255 (28 – 1) characters. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.
  • TEXT – This data type represents a text column with a maximum length of 65,535 (216 – 1) characters. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.
  • MEDIUMTEXT – This data type represents a text column with a maximum length of 16,777,215 (224 – 1) characters. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.
  • LONGTEXT – This data type represents a text column with a maximum length of 4,294,967,295 or 4GB (232 – 1) characters. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.
  • ENUM – This data type represents a string object having only a single value from a list.
  • SET – This data type represents a string object having zero or more values from a list, with a maximum of 64 members. SET values present internally as integer values.

Was this helpful?

Yes  No
Related Articles
  • MariaDB – Where Clause
  • MariaDB – Select Query
  • MariaDB – Insert Query
  • MariaDB – Drop Tables
  • MariaDB – Create Tables
  • MariaDB – Select Database
Leave A Comment Cancel reply

MariaDB
  • MariaDB – Data Types
  • MariaDB – Where Clause
  • MariaDB – Select Query
  • MariaDB – Insert Query
  • MariaDB – Drop Tables
  • MariaDB – Create Tables
View All 14  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org