Why is Varchar(255) in MySQL when 2^8 is 256?

If you look in many database implementations, you will see that VARCHAR limits are not at 256 (2^8), but they are at 255.

This 255/256 value is the number of characters storeable in the field. If the index starts at the 1st character, you would think that it should end at the 256th character, because 2^8 = 256.

According to the MySQL documentation, the size of a varchar field is actually "1 + (0 to 255 bytes)", or "2 + (256 to X bytes", if over 255.

Source: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

That delimiting mark, between 255 and 256 bytes, is defined not by logical limitations of data storage, but by the MySQL implementation itself. By using the 255 limit, you are using one less byte than if you had 256.

This is so that MySQL can count the length of a field (from 0 to 255, not from 1 to 256) in a single byte.

Be aware, though: If any of the characters are UTF-8, that means they will be multi-byte encoded, and this will definitely go over the 255 byte limit in MySQL and use the extra byte, even if it doesn't warn you of that.

What are MySQL's Text Max-Length Limitations?

If we look at the MySQL docs, we can see that there are certain limitations to the length of Text-type fields in the MySQL DB...

TINYBLOB, TINYTEXT: Can Store 255 Bytes (2^8 - 1 bytes)
BLOB, TEXT: Can Store 64 KB (2^16 - 2 bytes)
MEDIUMBLOB, MEDIUMTEXT: 16 MB (2^24 - 3 bytes)
LONGBLOB, LONGTEXT: 4GB (2^32 - 4 bytes)

Source: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

You may also want to consider CHAR() and VARCHAR() types when taking into consideration fields that can store text and their limitations.

What are the Best Practices for Database DateTime Storage?

Dates are datetimes are common values that coders are going to want to save. But what is the ideal format?

With MySQL, you have the options of Date (default "0000-00-00"), DateTime (default "0000-00-00 00:00:00"), and even Year ("default "0000").

Any of these should fit your purposes for storing recent data. The only limits are that Date and DateTime cannot store years older than 1000, and Year has even smaller range of valid values.

There are advantages to storing dates this way: timezones are built-in to MySQL which can be applied or unapplied easily, the data is fit into as few bits as possible to get you the best storage, you can always sort by using the "greater than" or "lesser than" operators, etc..

Some people store this value as a string. But that is quite a bit more data to hold into the system, and it will accept invalid date/datetime values. This should be avoided.

Some people store this value as an integer, using the epoch time schema. But epoch time is timezone-less, and there will be no way for MySQL to be able to adjust or unadjust this time for timezones. This should be avoided.

Use MySQL's Date or DateTime.

Why Custom-Delimited Data Sets are Bad and Should Be Avoided

With MySQL and other database options, you can store data to fields for tables. And sometimes, people are tempted to store a list to a field, like this: "1,2,3,4,5".

When the coder of this needs to go over the list, they retrieve it from the database, break it up by the commas using explode() or split(), depending on the language, and then iterate over the list.

There are a number of reasons why this is a problematic approach to whatever you're trying to solve.

The problem is that you need the whole list every time you want to do something with it.

Want to count how many 1's or 2's or 3's in the list? You must loop over the entire list.

Want to get only numbers when they are 4 or 5? You must loop over the entire list.

Want to delete just one value? Want to delete values greater than 5? Loop the list, loop the list, loop the list, and that's a lot of looping.

Besides performance issues, there are technical limitations.

How do you guarantee that something like "skyscraper" doesn't show up in your list of numbers? How do you escape characters like your delimiter (a comma in this case), which may appear in words, like this list: "barn", "skyscraper", "appartments, condos, etc."?

These performance and technical issues mean that few programmers actually implemented delimited lists. So, if you're going to use it in your project, it's going to be something that new people need to stress over and re-learn, when there are many more commonly accepted practices that solve this problem.

How to Detect the Character Set Encoding of a MySQL Database

You may want to know if your MySQL DB is encoded in latin1 or UTF-8, or maybe some other charset.

You can see the default character set encoding and collation with these commands...

SELECT @@character_set_database;
SELECT @@collation_database;

But this is just for the current database. There are various other default encodings and collations at different levels, which you will only be able to see with commands like these...

show variables like 'char%';
show variables like 'collation%';

If you want to see the encoding for a particular table, you can find its encoding with...

SHOW CREATE TABLE MyTable;

To see ONLY the encoding of a particular Table's field, you can search the INFORMATION_SCHEMA tables...

SELECT COLLATION_NAME
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'MySchema'
AND TABLE_NAME = 'MyTable'
AND COLUMN_NAME = 'MyField';

Full UTF-8 Web Application Development - How to Do It Right

With UTF-8 support, you can be guaranteed that any language will work with your code. But there are two fundamental things that you need to make sure happens:

First, convert your database to UTF-8. This is the data part of it.

To convert your database requires changing the default settings and converting the tables themselves. You can change the default settings with...

MyDB@sys mysql> ALTER DATABASE MyDatabase DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

And you can convert your tables with...

MyDB@sys mysql> ALTER TABLE MyTable DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
MyDB@sys mysql> ALTER TABLE MyTable MODIFY MyColumn VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';

Second, send all web pages with a UTF-8 header. This is the networking part of it.

For example, in PHP, to send the UTF-8 header, you will run this command being sending any output to the browser...

header('Content-type: text/html; charset=utf-8');

Every program, protocol, etc., has its exceptions and variations, so these are just the two basics you need to handle.

"bind_param() expects parameter 1 to be resource, boolean given" -- Possible Fixes?

Your MySQL and PHP code may be fully and logically thought-out. You may have even tested and verified it worked. But now you have this error:

"bind_param() expects parameter 1 to be resource, boolean given"

Your code probably looks something like this...

$db_link = new mysqli($hostname, $username, $password, $database);
$sql = "SELECT * FROM SomeTable WHERE id = ?";
$statement = $db_link->prepare($sql);
$statement->bind_param($_POST['id']);

How do you fix it? The problem is not with the bind_param() function, it's with the prepare() or the mysqli() functions!

If statement is a boolean, that means it is true or false, and in this case, false means that either mysqli() could not connect to the DB, or that prepare() had a problem.

If mysqli() has a problem, it could be a bad username/password combination, a networking issue, a bad db name, or something else. If prepare() has a problem, it could be a bad query, an unescaped term in the query, or something like that.

You can verify that mysqli() worked, by displaying its result and seeing that it's not false, and similarly with prepare().

Should Or Shouldn't I Use the MySQL_* functions in PHP?

There are a number of ways to access your MySQL server from PHP.

The first way, and the one originally taught, was to use the specialized MySQL functions in PHP, such as mysql_query(), mysql_connect(), and mysql_real_escape_string().

However, there are two problems with this function set.

First, it uses escaped strings, which are ultimately unsafer, slower, and less reliable than using Prepared Statements in MySQL.

Second, the developers behind the MySQL_* functions are no longer developing it, they no longer accept feedback on it, and they ask users to move onto something more reliable, with Prepared Statements, for MySQL uses.

This second reason is so strong, that all MySQL_* functions have been removed from PHP version 7.0 and up. Learn to do prepared statements, or these two reasons will perpetually haunt your code.

MySQL Limit Pagination is Slow on Large Offset Quantities - How do I resolve?

With MySQL field indexing on fields you regularly use in WHERE clauses, you can speed up your performance.

You can also use LIMIT to either limit your results, or to get paginated results. If you show 20 results per page and you want page 7's results, you would select with...

MyDB@sys mysql> SELECT * FROM ... LIMIT 7, 20;
...
20 rows in set (0.01 sec)

But this query becomes intensely slow with higher offsets, such as this query...

MyDB@sys mysql> SELECT * FROM ... LIMIT 50000, 20;
...
20 rows in set (0.35 sec)

The same exact query, using different offset limits, has very different performance!

But it is very easy to get around! Just use a subquery to select the id's first, which has your LIMIT.

MyDB@sys mysql> SELECT * FROM ... WHERE id IN(SELECT id FROM ... LIMIT 50000, 20);

Bypassing this problem shouldn't really work, and yet it does! That is because this has been a bug with MySQL for about the past decade: https://bugs.mysql.com/bug.php?id=41871

How do I remove a MySQL Index on a Table and Column?

MySQL indices can make your DB faster, but at times, you may need to remove them, for instance, when they are no longer needed or when a compound index is more appropriate.

For a MySQL DB named MyDB, you can remove a table index with...

MyDB@sys mysql> DROP INDEX `SomeIndexedColumn` ON `MyTable`;

Then, you can verify that this change took place, by showing the indices on the table afterwards...

MyDB@sys mysql> SHOW KEYS FROM MyTable;

Then you can proceed to add the indices or compound indices that your table needs.

MySQL is Not Using Index Key on Field - How do I resolve?

If you need to speed up your MySQL DB, you're adding indices on your columns. You will see a massive, immediate improvement in performance.

But sometimes you won't, and there are a number of ways to diagnose and resolve those problems.

1) Run the Analyze command on the table. For instance...

MyDB@sys mysql> ANALYZE `MyTable`

This tells your MySQL storage engine to look at the possible ways to join or select this table.

2) MySQL is using a different keys than the one you specified indices for. If your where clause is on multiple statements, you may want a compound index to make it more clear to the storage engine...

MyDB@sys mysql> ALTER TABLE `MyTable`
ADD INDEX `ComboIndex` (`Column1`,`Column2`)

And this will work with...

MyDB@sys mysql> SELECT .... WHERE
Column1 = "ABC" OR COLUMN2 = "DEF";

3) Your query may be simply too complicated for MySQL to grasp. In this case, simplifying the query into multiple UNION'd statements will produce the desired output at amazing speed...

MyDB@sys mysql> (SELECT .... WHERE
Column1 = "ABC") UNION (SELECT .... WHERE COLUMN2 = "DEF");

How do I add a MySQL Index Key to Improve Select Performance?

If you have a MySQL database, you can improve performance by adding index keys.

Indexing makes it so that SELECT statements will execute faster, when you have a WHERE clause that uses an indexed key. The speed different can be insignificant (on small DB's), or tremendous (on bigger DB's).

Add a MYSQL index to a column like this....

MyDB@sys mysql> ALTER TABLE `MyTable` ADD INDEX `MyColumn` (`MyColumn`);

Then you will get better performance with statements like...

MyDB@sys mysql> SELECT * FROM MyTable WHERE MyColumn = 'SomethingIWant';

You can also verify the keys a table has with....

MyDB@sys mysql> SHOW KEYS FROM MyTable;

Or, even with this....

MyDB@sys mysql> SHOW CREATE TABLE MyTable;

How do I list all tables in a MySQL Database?

If you have a MySQL database named MyDB, you can see all of the tables with...

MyDB@sys mysql> show tables;

If you have a bit more time, you can see all the tablenames for just the current DB with...

MyDB@sys mysql> select TABLE_NAME from INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = 'MyDB';

Or, if you want to see all tablenames for all databases in MySQL, you can run this....

MyDB@sys mysql> select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.tables;

You're now on your way to seeing all the database information.

How to Fix Getting Errcode 28 on MySQL?

Query3 failed: Error writing file '/tmp/MY9uc2x7' (Errcode: 28)

If you are seeing this error, it is not a MySQL Error Code, but a Linux OS Error Code.

See the full message with the C++ debugging tool, perror:

perror 28

And this will output:

OS error code 28: No space left on device

So the problem is easily solved! Delete some stuff on the server hosting your DB.