He’s adding and subtracting 1 from the title itself. The problem is he’s trying to add and subtract a value (1 or -1) from the length of the title, but that’s not what he’s doing at all. The query is presumably trying to get the last character from a string of text, or some variation of that. If it were title-1 the last two chars returned. Mysql> select c from t where c+10 select c from t where c+10 select c from t where c+10 select c, c+10, ''+interval 2 day from t where c+10 SELECT RIGHT(title,LENGTH(title+1)) FROM news > returns the last character. We’ll look for rows where the date is before January 21st. Now let’s execute a query that has an expression involving a date and a number on one side, and a string on the other. What is the underlying type of a date… is it a string, a number, some composite type? To try to find out, let’s fill a small table with some dates, using the date column type: Let’s keep looking at some more examples and see what else we can find.ĭate and time expressions in MySQL often look like strings, but you can do math on these types too. Looks like a lot is going on behind the scenes. Presumably the value 2 was also treated as a float, hence it became 2.0.The empty string was turned into the floating-point value 0.0. ![]() The value hello world was truncated to the empty string.Apparently string conversion to numbers works by throwing away non-numeric suffixes (more on this later).That’s interesting - it shows that a few things happened: Mysql> show warnings +-+-+-+ | Level | Code | Message | +-+-+-+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'hello world' | +-+-+-+ Why? Looking at the warnings from that statement can help: What is the result of that expression, and what is its type? It is the number 2. But what if you add a string that’s not numeric? This ability to automatically convert between strings and numbers is very handy in some cases. Does the following expression produce the number 7, or the string 52? I’ll start with a simple example: it’s actually possible to add a character string to a number. Let’s look at MySQL in action and see if we can figure out how it’s handling expression types. Sometimes, you might wonder how does this query work and exactly what’s happening to the variables in these expressions? Importantly, does it always work right? Yet, internally, every expression in MySQL has a type, and it does conversions amongst them as needed. In this respect, MySQL is much more like a dynamically typed language such as PHP or Perl - a DWIM (do what I mean) typing system. ![]() In strongly typed languages like Java, for example, typing is very strict. If you compare this to a lot of programming languages, you’ll find that it’s not always the way things work. When you send a query to MySQL, you usually don’t need to think explicitly about the types of the expressions in your query.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |