Varchar Requires a Length When Rendered on MySQL

VARCHAR requires a length when rendered on MySQL

I don't have a MySQL installation, and pas.plugins.sqlalchemy works fine on postgresql for me, but it would seem that the authors have made an assumption about varchars. Assuming it's not something that SQLAlchemy should be handling itself (it would be really nice if the MySQL dialect for SQLalchemy would select an appropriate maximum size for unbounded varchars), I'll see if I can commit a fix this evening.

A quick glance at the code shows that all "String" (treated as varchar by the database) fields have maximum lengths except Login, name and password in the User table and name in the Group table, and there's no good reason why these should be different.

Update: Check out https://svn.plone.org/svn/collective/PASPlugins/pas.plugins.sqlalchemy/branches/auspex from subversion. It's my version of pas.plugins.sqlalchemy with support for the IGroupCapability interface (lets users be added to and removed from groups that are also stored in the rdb), and I've also added lengths to all unbounded String fields.

If you don't know how to use subversion checkouts in buildout, see: http://pypi.python.org/pypi/mr.developer/

Importance of varchar length in MySQL table

No, in the sense that if the values you're storing in that column are always (say) less than 50 characters, declaring the column as varchar(50) or varchar(200) has the same performance.

Define the length of varchar for MySQL in rails

There's a number of options when creating columns that are documented for the column method. They also apply to add_column when doing subsequent modifications.

The most concise way to make a more limited column is:

t.string :name, :limit => 20
t.string :age, :limit => 6

As a note, it's highly unusual to impose limits like this in your database and a better solution is to limit on the model using validates. For example:

validates :name,
  :presence => true,
  :length => { :maximum => 20 }

MySQL has a tendency to truncate values that are too long without telling you, so not having a length limit will eventually lead to lost data, especially with such a short length.

Remember that VARCHAR columns in the database are variable length, so there's no storage advantage to a ten character value in a VARCHAR(255) versus a VARCHAR(20).

Varchar(32) won't accept zero MySQL

The error you're getting has nothing to do with how your database is set up. empty goes true if you have a zero value.

The following things are considered to be empty:

"" (an empty string)

0 (0 as an integer)

0.0 (0 as a float)

"0" (0 as a string)
NULL

FALSE
array() (an empty array)

$var; (a variable declared, but without a value)

http://us3.php.net/empty

If you would like to do a correct check, try isset and simply checking if your post == ""
so for example

if(isset($_POST['mileage']) && $_POST['mileage'] != "")

MySQL - length() vs char_length()

LENGTH() returns the length of the string measured in bytes.

CHAR_LENGTH() returns the length of the string measured in characters.

This is especially relevant for Unicode, in which most characters are encoded in two bytes. Or UTF-8, where the number of bytes varies. For example:

select length(_utf8 '€'), char_length(_utf8 '€')
--> 3, 1

As you can see the Euro sign occupies 3 bytes (it's encoded as 0xE282AC in UTF-8) even though it's only one character.

VARCHARS: 2, 4, 8, 16, etc.? Or 1, 3, 7, 15, etc.?

In other words, if I have a number that I know will never be above 12, should I just go ahead and use a VARCHAR(15) or VARCHAR(16) instead?

No! Use varchar(12) (or maybe even char(12) if the length is fairly constant ).

Once upon a time the varchar type was limited to 255 characters on some systems (including MySql prior to 5.0.3) because the first byte stored indicated the length of the field. Given this restriction, devs wanting to allow a reasonable amount of text would choose 255 rather than going to a different data type altogether.

But if you know the size of your data, definitely use exactly that size for the database.

text or varchar?

The varchar data type in MySQL < 5.0.3 cannot hold data longer than 255 characters. While in MySQL >= 5.0.3 it has a maximum of 65,535 characters.

So, it depends on the platform you're targeting, and your deployability requirements. If you want to be sure that it will work on MySQL versions less than 5.0.3, go with a text type data field for your longer column

• http://dev.mysql.com/doc/refman/5.0/en/char.html

---

Related Topics