Data | JamesCoyle.net Limited

2-Oct-2019
James Coyle
0

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 Type	Size	Description
CHAR(n)	255 characters	Fixed-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.
TINYTEXT	255 bytes	Variable-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
ENUM	2 bytes	The 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.

3-Oct-2018
James Coyle
0

List of Bank Holidays For England in SQL Format

Tags : Data Database MariaDB MySQL PostgreSQL SQL

Category : How-to

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!

JamesCoyle.net Limited

Mysql Data Types and Sizes for String/ Text

0

Mysql Data Types and Sizes for String/ Text

0

List of Bank Holidays For England in SQL Format

Visit our advertisers

Search

Quick Poll

Recent Posts

Visit our advertisers