How to Store More Than 255 Char in MySQL Database

How to store more than 255 char in MySQL database?

Prior to MySQL 5.0.3, a VARCHAR could only store up to 255 characters.

To store up to 65535 (64KB) characters, use a TEXT column.
To store up to 16777216 (16MB ) characters, use a MEDIUMTEXT column.
To store up to 4294967296 (4GB) characters, use a LONGTEXT column.

See the storage requirements section of the manual for caveats on their usage.

Versions of MySQL after 5.0.3 can store up to 65535 chars in a VARCHAR (However you cannot store more than 65535 bytes in a single row).

Recommendation on database schema for a text column storing more than 255 characters

You can use varchar(500) for newer versions.

What is the MySQL VARCHAR max size?

Is it wise to declare a VARCHAR with a value greater than 255 in MySQL?

As the answer @Eric pointed out suggests, VARCHARs are stored in table while TEXTs are stored in a separate file - the only truly important point that you have to keep in mind when designing a table structure is the row size limitation (MySQL limits each row / record to 65 KB).

I suggest you use VARCHARs for "one-liners" - anything that has a text input as its data source.

Key longer than 255 chars in a UTF8 mysql database

Best, and possibly only solution: Don't use urls as foreign keys at all.

Put the urls in a table with a surrogate key (auto increment integer, for instance). Use that key for foreign keys and just put a unique index on the url column to prevent duplicate urls.

Ensuring uniqueness on a varchar greater than 255 in MYSQL/InnoDB

For MySQL 5.7 or newer, see Andre Dalcher's answer for a nicer way using generated columns.

You could use a SHA1 hash of the url as the unique key. There is a chance that two urls have the same hash, but the probability of that is so ridiculously small, that for practical purposes this method should work fine.

You could also set up a trigger so the hash column is computed automatically whenever you INSERT:

CREATE TRIGGER mytrigger
BEFORE INSERT
ON foo
FOR EACH ROW SET
    NEW.hash = SHA1(NEW.url)

Is there a fixed length version of MySQL's CHAR data type that can store more than 255 characters?

No, there is no such datatype. Did you actually measure if using CHAR in place of VARCHAR gives you any measurable performance gain? Remember what they say about premature optimization?

Splitting message into many columns and then stitching it together will probably be slower, than any increase in speed gained from using CHAR instead of VARCHAR.

SQL for string more than 255 chars

Assuming SQL Server, try this which uses the LEN function combined with a CASE:

SELECT 
    CASE WHEN LEN(StringColumn) > 255 
        THEN 'value more than 255 characters' 
        ELSE StringColumn 
    END As MyColumnName
FROM MyTable

Example sqlfiddle: http://www.sqlfiddle.com/#!6/e0508/1/0

Edit: In case you are using Oracle as the tags suggest, instead of LEN, use LENGTH:

SELECT 
    CASE WHEN LENGTH(StringColumn) > 255 
        THEN 'value more than 255 characters' 
        ELSE StringColumn 
    END As MyColumnName
FROM MyTable

Example sqlfiddle: http://www.sqlfiddle.com/#!4/9bc74/2/0

What's new for no more varchar(255)

What "VARCHAR(255) rule" are you referring to?

Each database vendor is free to implement VARCHAR however they want to. The rules (and guidelines) for VARCHAR isn't necessarily going to be the same for every databsase.

As far as the SQL standard, I haven't really looked into it. It might be pretty loose, so all the VARCHAR implementations are all found to comply with the standard. If the SQL standard for VARCHAR is really strict, then DBMS vendors may either extend the standard, or just may not be compliant. I don't think the actual standard matters all the much. What matters is the actual rules enforced by the DBMS.

As far as a general guideline, specify a VARCHAR length long enough to support the system requirements. If the requirement of the system is to allow no more than 200 characters, then I'd specify the length as VARCHAR(200).

As another general guideline, don't define VARCHAR lengths that that are larger than they need to be. VARCHAR columns declared longer than necessary can have an impact on resources and performance.

Oracle limit for VARCHAR length is 4000 characters. (In previous versions of Oracle, the maximum was 2000. If you need more than 4000 characters, then you could use CLOB datatype.

SQL Server limit to VARCHAR(8000), unless you specify VARCHAR(MAX) which allows a maximum size (in bytes) of 2^32-1.

MySQL has limit of 65,535 for maximum row length limit. So that effectively limits size of VARCHAR to VARCHAR(21844), if using a multibyte characterset like utf8. With a single byte characterset (like latin1), the maximum would be VARCHAR(65532). If you need more characters than that, or you run into the limit on the maximum row length, you could use TEXT datatype instead of VARCHAR.

Most DBMS VARCHAR implementations store a "length" field for a VARCHAR column, along with the value. the length is stored as an integer.

In some DBMS, if the maximum length (in bytes) of a VARCHAR column doesn't exceed 255 bytes, the length field can be implemented as a single byte integer. If the column allows more than 255 bytes, then the length field has to be larger than a single byte.

With dynamic row formats, in terms of row storage, storing 10 characters in a column, it doesn't really matter if the column is defined as VARCHAR(30) or VARCHAR(1000). With fixed row formats, the space for the maximum length of the column will be reserved. The format for row storags is going to depend on the DBMS, and in some cases (MySQL) on the storage engine and the specified row format.

Yes, it's 2016. And we've come a long way since the introduction of the first commercial relational database system.

The database is only one part of the system. There may be limits in the application or other software components. (If the application is written in C, and the application is defining a structure with a byte array for the field, the limit on the size there is going to be important. Increasing the size allowed in the database won't automatically fix the application.

There could also be length limits/restrictions in Javascript code, or in HTML elements of a web page. Or there can be limitations of other software components. For example, some of the really old SQL Server ODBC drivers have a limit of 255 characters (bytes?) for both CHAR and VARCHAR columns.

So the length of a VARCHAR in the database is only part of the story.

With all of that said, I'm still not clear what you mean, when you ask

Can we break the VARCHAR(255) rule?

I'm wondering what "rule" you are referring to. In most every database I'm aware of, it's possible to define VARCHAR columns much longer than 255 bytes, or 255 characters. And doing that doesn't break any rule.

How to Store More Than 255 Char in MySQL Database