mysql query showing wrong result
I have a database table look like this
+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 100 | name | surname |
| 101 | name | surname |
| 102 | name | surname |
+===============================+
When i run this query which should show me no rows because there is no row with 101foo2
value :
SELECT * FROM tableName WHERE ID = '101foo2'
I am getting a result with same ID without the foo2
word
+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 101 | name | surname |
+===============================+
how it is showing the row with ID 101
if my query is ID = '101foo2'
mysql sql phpmyadmin
add a comment |
I have a database table look like this
+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 100 | name | surname |
| 101 | name | surname |
| 102 | name | surname |
+===============================+
When i run this query which should show me no rows because there is no row with 101foo2
value :
SELECT * FROM tableName WHERE ID = '101foo2'
I am getting a result with same ID without the foo2
word
+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 101 | name | surname |
+===============================+
how it is showing the row with ID 101
if my query is ID = '101foo2'
mysql sql phpmyadmin
1
Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting'101foo2'
into101
if the column datatype is a INT.. That why your query results into getting that record.
– Raymond Nijland
Jan 3 at 17:13
add a comment |
I have a database table look like this
+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 100 | name | surname |
| 101 | name | surname |
| 102 | name | surname |
+===============================+
When i run this query which should show me no rows because there is no row with 101foo2
value :
SELECT * FROM tableName WHERE ID = '101foo2'
I am getting a result with same ID without the foo2
word
+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 101 | name | surname |
+===============================+
how it is showing the row with ID 101
if my query is ID = '101foo2'
mysql sql phpmyadmin
I have a database table look like this
+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 100 | name | surname |
| 101 | name | surname |
| 102 | name | surname |
+===============================+
When i run this query which should show me no rows because there is no row with 101foo2
value :
SELECT * FROM tableName WHERE ID = '101foo2'
I am getting a result with same ID without the foo2
word
+======+===========+============+
| ID | user Name |user surname|
+======+===========+============+
| 101 | name | surname |
+===============================+
how it is showing the row with ID 101
if my query is ID = '101foo2'
mysql sql phpmyadmin
mysql sql phpmyadmin
asked Jan 3 at 17:10
NasserNasser
74
74
1
Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting'101foo2'
into101
if the column datatype is a INT.. That why your query results into getting that record.
– Raymond Nijland
Jan 3 at 17:13
add a comment |
1
Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting'101foo2'
into101
if the column datatype is a INT.. That why your query results into getting that record.
– Raymond Nijland
Jan 3 at 17:13
1
1
Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting
'101foo2'
into 101
if the column datatype is a INT.. That why your query results into getting that record.– Raymond Nijland
Jan 3 at 17:13
Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting
'101foo2'
into 101
if the column datatype is a INT.. That why your query results into getting that record.– Raymond Nijland
Jan 3 at 17:13
add a comment |
1 Answer
1
active
oldest
votes
You are mixing types. ID
is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101
and matches.
You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:
SELECT * FROM tableName WHERE ID = 101foo2
However, you would get an error. Another possibility is to force the conversion to a string:
SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54026797%2fmysql-query-showing-wrong-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You are mixing types. ID
is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101
and matches.
You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:
SELECT * FROM tableName WHERE ID = 101foo2
However, you would get an error. Another possibility is to force the conversion to a string:
SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'
add a comment |
You are mixing types. ID
is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101
and matches.
You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:
SELECT * FROM tableName WHERE ID = 101foo2
However, you would get an error. Another possibility is to force the conversion to a string:
SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'
add a comment |
You are mixing types. ID
is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101
and matches.
You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:
SELECT * FROM tableName WHERE ID = 101foo2
However, you would get an error. Another possibility is to force the conversion to a string:
SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'
You are mixing types. ID
is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101
and matches.
You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:
SELECT * FROM tableName WHERE ID = 101foo2
However, you would get an error. Another possibility is to force the conversion to a string:
SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'
answered Jan 3 at 17:13
Gordon LinoffGordon Linoff
793k36316419
793k36316419
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54026797%2fmysql-query-showing-wrong-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Rule 1 if the datatype is a INT type don't use strings to fetch it. MySQL trys auto converting
'101foo2'
into101
if the column datatype is a INT.. That why your query results into getting that record.– Raymond Nijland
Jan 3 at 17:13