Mysql Data Types and Sizes for String/ Text

Mysql Data Types and Sizes for String/ Text

Category : How-to

Get Social!

MySQL or MariaDB has several data types for handling text-based characters. There are several data types for handling smaller strings such as CHAR and VARCHAR data types. For larger text-based strings there are BLOB based data types such as TEXT.

It’s worth noting at this point that the below-quoted sizes do not necessarily represent the number of characters they can hold. In addition, more recent versions of MySQL (version 5 and 8) counts characters when defining the length, however, prior to these versions byres were used.

The below table shows the ‘size’ of each data type – notice that some data types are mentioned in characters, and others in bytes. The number of characters are always used when defining a string data type in your DDL statement – for example, VARCHAR(10).

Data TypeSizeDescription
CHAR(n)255 charactersFixed-length character field. Rows are padded with whitespace to the defined length.
VARCHAR(n)65,535 bytes *Variable-length character field with no manipulation on INSERT or SELECT.
TINYTEXT255 bytesVariable-length and stored off-row. Can only be sorted and grouped by up to max_sort_length characters
TEXT(n)65,535 bytes Variable-length and stored off-row. Can only be sorted and grouped by up to max_sort_length characters
MEDIUMTEXT(n)16,777,215 bytes (16MB)Variable-length and stored off-row. Can only be sorted and grouped by up to max_sort_length characters
LONGTEXT(n)4,294,967,295 bytes (4GB)Variable-length and stored off-row. Can only be sorted and grouped by up to max_sort_length characters
ENUM2 bytesThe ENUM doesn’t need a size definition but can hold up to 65,535 values.

* The maximum row length in MySQL is 65,535 bytes – your total row size cannot exceed this byte value. Keep in mind that some character sets consume more than one byte per character. For example, utf8mb4 can take up to 4 bytes per character and therefore the maximum VARCHAR is approximately one-quarter of the maximum row size.


List of Bank Holidays For England in SQL Format

Get Social!

First off let’s create a table to store the bank holiday values. You may need to adjust this slightly depending on your SQL server technology being used (this was tested on MySQL/ MariaDB Server) but this is standard SQL dialect and should work on any RDBMS that respects the current SQL standards.

DROP TABLE IF EXISTS bank_holidays;
CREATE TABLE bank_holidays (
  holiday_date date NULL
, holiday_description INT NULL
, PRIMARY KEY (holiday_date)
);

The next step is to insert the bank holiday values below. This table is currently for 2012 up to 2019 for England and Wales.

INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190419', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190422', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190506', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190527', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190826', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20191225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20191226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20181226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20181225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180827', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180528', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180507', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180402', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180330', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20171226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20171225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170828', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170529', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170501', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170417', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170414', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170102', 'New Year’s Day (substitute day)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20161227', 'Christmas Day (substitute day)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20161226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160829', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160530', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160502', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160328', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160325', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20151228', 'Boxing Day (substitute day)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20151225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150831', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150525', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150504', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150406', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150403', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20141226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20141225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140825', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140526', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140505', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140421', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140418', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20131226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20131225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130826', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130527', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130506', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130401', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130329', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20121226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20121225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120827', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120605', 'Queen’s Diamond Jubilee (extra bank holiday)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120604', 'Spring bank holiday (substitute day)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120507', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120409', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120406', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120102', 'New Year’s Day (substitute day)');

For a more up to date list, please see my github page.

Please submit a PR if you have collated a list yourself, or have any updates to an existing list. The repository currently covers the UK, but I’d be more than happy to accept a PR for other countries! 


View Table Sizes in MySQL/ MariaDB Databases

Get Social!

MySQL and MariaDB present a bunch of queryable objects that give you all sorts of insights into what’s happening with the database. 

The size of data stored in tabels is one such thing that can be easily queried directly in SQL, providing you have SELECT access to the information_schema. 

Create the two below views in a schema of your choice:

CREATE  OR REPLACE VIEW schema_size AS
SELECT   table_schema
,        round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM     information_schema.tables  
WHERE table_schema IN ('dv', 'da', 'hue')  
GROUP BY table_schema
ORDER BY table_size_mb DESC;

CREATE  OR REPLACE VIEW table_size AS
SELECT   table_schema
,        table_name 
,        round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM     information_schema.tables  
WHERE table_schema IN ('dv', 'da', 'hue')  
GROUP BY table_schema
,        table_name 
ORDER BY table_size_mb DESC;

See schema_size and table_size for more info.

You can now query each view to get the size, in megabytes, of a whole schema or individual table respectivley. 

SELECT * 
FROM   schema_size;

SELECT * 
FROM   table_size;
  • schema_size – displays the aggregated size of each schema in the database.
  • table_size – lists all tables in the database. You can add a filter to the table_schema column to limit the output.

MySQL/ MariaDB Table Size

Get Social!

Create a MySQL or MariaDB view to show the size of each table in the database:

See database size for more information.

CREATE  OR REPLACE VIEW table_size AS
SELECT   table_schema
,        table_name 
,        round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM     information_schema.tables  
WHERE table_schema IN ('dv', 'da', 'hue')  
GROUP BY table_schema
,        table_name 
ORDER BY table_size_mb DESC

MySQL/ MariaDB Schema Size

Get Social!

Create a MySQL or MariaDB view to show the aggregated size of each schema in the database:

See database size for more information.

CREATE  OR REPLACE VIEW schema_size AS
SELECT   table_schema
,        round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM     information_schema.tables  
WHERE table_schema IN ('dv', 'da', 'hue')  
GROUP BY table_schema
ORDER BY table_size_mb DESC

Oracle ORA-02287: sequence number not allowed here

Get Social!

I’ve recently hit an issue when trying to include a sequence.nextval in an Oracle database view. the database throws the following error:

ORA-02287: sequence number not allowed here

I can see why that might not be allowed – each time you select from the view you’ll get a new sequence number making it a bit daft for most circumstances.

I say most because I’ve had a need recently; ETL logic at my current client is held in database views with a boilerplate set of code that wraps around that to perform the table load. That’s fine until you want to fabricate ID’s (SIDs) as part of the load.

Solution

The solution here was to create a utility package to wrap the sequence that could be referenced in the view. See the below example code:

CREATE SEQUENCE seq_test 
  MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
   l_nextval NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
         INTO l_nextval;

   RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS 
SELECT   func_seq_test('seq_test') seq_id
,        dummy
FROM     dual
/

You can now query the view without any pesky ORAs.

SELECT * FROM view_seq_test
/

    SEQ_ID D
---------- -
         1 X

1 row selected.

See the test script here.


Visit our advertisers

Search

Quick Poll

Do you use ZFS on Linux?

Visit our advertisers