Sql datatypes: Difference between revisions

From wikinotes
(Created page with "{{ TODO | is this all SQL? or is this MySQL specific? }} {| class="wikitable" !colspan=2| Integers |- | <code>UNIQUE</code> || I need to lookup how this works....")
 
No edit summary
Line 1: Line 1:
{{ TODO |
It is difficult to confirm if these are all officially part of the SQL spec without purchasing it.<br>
is this all SQL? or is this MySQL specific? }}
Always consult your database docs.


{| class="wikitable"
= Text =
!colspan=2| Integers
<blockquote>
|-
<syntaxhighlight lang="SQL">
| <code>UNIQUE</code>               || I need to lookup how this works. IMPORTANT
CHAR(10)    # (N-bytes) string. If stores shorter string, remaining space filled with blank characters.
|-
VARCHAR(10) # (N-bytes) string. Can store shorter strings
| <code>BOOL</code>                  || (alias TRUE/FALSE to TINYINT)
</syntaxhighlight>
|-
</blockquote><!-- Text -->
| <code>TINYINT</code>               || int (-128 to 127)
 
|-
= Blobs =
| <code>SMALLINT</code>             || int (-32768 to 32767)
<blockquote>
|-
<syntaxhighlight lang="SQL">
| <code>MEDIUMINT</code>            || int (-8388608 to 8388607)
BLOB        # (1-byte) binary blob
|-
BLOB(10)    # (N-bytes) binary blob
| <code>INT</code>                   || int (-2147483648 to 2147483647)
</syntaxhighlight>
|-
</blockquote><!-- Binary Blobs -->
| <code>BIGINT</code>                || int (-9223372036854775808 to 9223372036854775807)
 
|-
= Numbers =
!colspan=2| Float & Double
<blockquote>
|-
<syntaxhighlight lang="SQL">
| <code>FLOAT</code>                || float (a very big number in scientific notation)
BOOLEAN          # true/false, TRUE/FALSE
|-
 
| <code>DOUBLE</code>                || double (an even bigger number in scientific notation)
SMALLINT          # (2-bytes, signed)                               -32_768 ... 32_767
|-
 
!colspan=2| Dates
INT              # (4-bytes, signed)                         -2_147_483_648 ... 2_147_483_647
|-
 
| <code>DATE</code>                 || 2014-01-01
BIGINT           # (8-bytes, signed)             -9_223_372_036_854_775_808 ... -9_223_372_036_854_775_807
|-
 
| <code>DATETIME</code>              || 2014-01-01 12:30:00
DECIMAL          # (10-digits, 0-decimal-places)            -1_000_000_000 ... 1_000_000_000
|-
DECIMAL(10, 2)   # (10-digits, 2-decimal-places)          -1_000_000_000.00 ... 1_000_000_000.00
| <code>TIMESTAMP</code>            || 1970-01-01 00:00:01 (seconds since UNIX EPOCH)
 
|-
FLOAT(10)         # (N-bytes, )
!colspan=2| Strings
 
|-
DOUBLE            #
| <code>CHAR(4)</code>              || store limited number of characters. byte size is equivalent to number of characters (maxLength 254)
</syntaxhighlight>
|-
</blockquote><!-- Numbers -->
| <code>VARCHAR(4)</code>           || store limited number of characters. byte size only as large as needs to be (note that 1-2 bytes are used to store byte size) (maxLength 254)
|-
| <code>BINARY(4)</code>             || store limited number of binary characters. byte size is equivalent to number of characters (maxLength 254)
|-
| <code>VARBINARY(4)</code>         || store limited number of binary characters. Binary version of VARCHAR. (maxLength 254)
|-
| <code>TINYBLOB/TINYTEXT</code>    || Binary/String (maxLength 256 chars)
|-
| <code>BLOB/TEXT</code>            || Binary/String (maxLength 65536 chars)
|-
| <code>MEDIUMBLOB/MEDIUMTEXT</code> || Binary/String (maxLength 16777216 chars)
|-
| <code>LONGBLOB/LONGTEXT</code>     || Binary/String (maxLength 4294967296 chars)
|}

Revision as of 17:08, 19 September 2021

It is difficult to confirm if these are all officially part of the SQL spec without purchasing it.
Always consult your database docs.

Text

CHAR(10)     # (N-bytes) string. If stores shorter string, remaining space filled with blank characters.
VARCHAR(10)  # (N-bytes) string. Can store shorter strings

Blobs

BLOB         # (1-byte)  binary blob
BLOB(10)     # (N-bytes) binary blob

Numbers

BOOLEAN           # true/false, TRUE/FALSE

SMALLINT          # (2-bytes, signed)                                -32_768 ... 32_767

INT               # (4-bytes, signed)                         -2_147_483_648 ... 2_147_483_647

BIGINT            # (8-bytes, signed)             -9_223_372_036_854_775_808 ... -9_223_372_036_854_775_807

DECIMAL           # (10-digits, 0-decimal-places)             -1_000_000_000 ... 1_000_000_000
DECIMAL(10, 2)    # (10-digits, 2-decimal-places)          -1_000_000_000.00 ... 1_000_000_000.00

FLOAT(10)         # (N-bytes, )

DOUBLE            #
Retrieved from "http://willpittman.net:8080/index.php?title=Sql_datatypes&oldid=27561"