Perhaps you’re familiar with MySQL’s SERIAL type, which is not actually a data type, but a shortcut for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
One restriction of SERIAL is that it forces us to use the BIGINT data type, which is fine if you think you’ll need the extra space. But what if you want the same attributes for a smaller integer type, like INT or MEDIUMINT for example?
Enter SERIAL DEFAULT VALUE.
In MySQL, a DEFAULT constraint is used to provide a default value for a column when no value is specified during an INSERT operation. This is particularly useful for ensuring that important columns have consistent, non-null values, even when omitted from inserts.
In this article, we’ll use MySQL to create a table with a couple of DEFAULT constraints, and we’ll also add a constraint to that table after it has been created.
If you’re getting MySQL error 1066 which reads something like “1066 (42000): Not unique table/alias: ‘products’” when using the HANDLER statement in MySQL, it could be that you’re trying to open a table that’s already open.
If this is the case, be sure to close the table before trying to open it again. Or simply continue working without opening the table again.
Continue readingMySQL provides us with the ability to create server-side prepared statements, which are precompiled SQL queries that can be executed multiple times with different arguments.
We can use the performance schema to return a list of all prepared statements in the server, along with useful information about each prepared statement.
Continue readingIf you’re getting an error that reads something like “1052 (23000): Column ‘name’ in field list is ambiguous” in MySQL, it looks like you could be referencing a column name in a query without qualifying it with the table name.
This can happen when you perform a join between tables that use the same name for one or more columns.
To fix this issue, be sure to qualify column names with the table names when performing joins across tables.
Continue readingMySQL provides us with the ability to create multiple prepared statements and run them as many times as we like, while changing the parameter values with each run.
While prepared statements are only available to the session that created them, they are stored in the server. So it’s quite possible that a server could have a build up of lots of prepared statements hanging around once different users have created them.
Continue readingIf you’re getting an error that reads something like “ERROR 1066 (42000): Not unique table/alias: ‘d’” in MySQL, it could be that you’re trying to assign a duplicate alias to a table. Or it could be that you’re doing a self-join without assigning table aliases.
Table names and aliases must be unique when doing queries in MySQL.
The error can also happen if you use HANDLER to open a table, but then try to open it again before closing it.
To fix this issue, be sure to use unique table aliases in your query. And if you’re using HANDLER, either close the table or continue working with it (without trying to open it again).
If you work with MySQL databases, you may have encountered the SERIAL type in a table’s definition. And if you’ve come over from PostgreSQL, you might have a false expectation about how MySQL’s SERIAL works – unless you already know 😉
Below is a quick overview of MySQL’s SERIAL type, including an explanation of how it differs from PostgreSQL’s SERIAL type and similar functionality from other DBMSs.
If you’re getting MySQL error 1109 which reads something like “1109 (42S02): Unknown table ‘products’ in HANDLER” in MySQL, it appears that you’re trying to reference a table that’s not currently open when using MySQL’s HANDLER statement.
This can happen when you try to read from a table that you haven’t yet opened. It can also happen when you’ve assigned an alias to the table but you try to reference it without the alias. And it can happen if you try to close a table that isn’t actually open.
Continue readingIn MySQL, a UNIQUE constraint is a constraint type that ensures that all values in a column or a group of columns are distinct from each other. In other words, all values that go into the column or group of columns must be unique.
UNIQUE constraints can be applied whenever we want to prevent duplicate entries in specific columns without making them a primary key.