It would be nice if you could use a column type to determine if a character can be inserted into your row, but it is not possible. You should write a column type to help you determine if a character is permissible.
SQL is all about the type of data a particular column will hold. It will not allow you to insert a character that is not a valid character. In a database of information it isn’t allowed to write in the wrong type (that’s the whole point of a database), so you should always be aware of the type of data you are dealing with.
The issue with SQL is that it only allows you to store information that can be converted into a string. It also doesnt allow you to store the type of the data. So lets say you are storing information about your friend Chris’s name, you should not be storing information that isnt a name. Since your friend is named Chris, its a valid column type.
MySQL may not be the best choice for storing information about your friend, but if its not a name, its a character. Thats the whole point of a database. You can of course store a char, but that can be very confusing to your database. To store an apostrophe you would need to use a character type that allows you to store apostrophes.
I don’t know if you’re aware but, char is not a valid type for storing a apostrophe. Its a special type of char that is used as a placeholder for an apostrophe.
so when you do a table scan SQL looks for a column with a particular type and then casts it to that type.
One of the few exceptions to this rule is if you have a table containing strings. Here you would have to use a string type that allows you to store strings. For example, in my MySQL database I store all strings as VARCHAR(8000) and the char type is VARCHAR(8000) too.
Although it’s not an exception, some types of strings are not valid in character data types. I’m not sure what this means, but it could be an issue if some of your data is being stored as a string.
This is one of the reasons you should never store data as strings in a database. The reason being that you cannot store the correct characters in that data type, only in specific bytes. As a result, MySQL will only allow you to store a string of a certain length in a column.