Re: does mySQL support a boolean data type?
why don't you use int(1) and set it to 0 or 1? -yves -Ursprngliche Nachricht- Von: Dan Anderson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Sonntag, 13. Juli 2003 18:41 Betreff: does mySQL support a boolean data type? I ran a search through the mySQL manual and google and could not find a satisfactory answer. Does mySQL support the declaration of a boolean data type? Currently I am using VARCHAR(6)s with either 'TRUE' or 'FALSE' and would like to cut down on the storage. Thanks in advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does mySQL support a boolean data type?
ehm, why do i say INT(1) then, and not just INT? but one could also take a 1 char type and perform some AND and OR on that one by hand. it's not really fast but it's possible right now. and it should be possible to convert the table later (for version 5.1 maybe) and with a little abstraction in the programme code, it shouldn't be too hard. -yves -Ursprngliche Nachricht- Von: Phil Bitis [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Sonntag, 13. Juli 2003 19:57 Betreff: Re: does mySQL support a boolean data type? int(1) takes up 4 bytes worth of space, and just displays 1 character. BIT or TINYINT(1) take up 1 byte. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, July 13, 2003 6:05 PM Subject: Re: does mySQL support a boolean data type? why don't you use int(1) and set it to 0 or 1? -yves -Ursprngliche Nachricht- Von: Dan Anderson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Sonntag, 13. Juli 2003 18:41 Betreff: does mySQL support a boolean data type? I ran a search through the mySQL manual and google and could not find a satisfactory answer. Does mySQL support the declaration of a boolean data type? Currently I am using VARCHAR(6)s with either 'TRUE' or 'FALSE' and would like to cut down on the storage. Thanks in advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can mysql handle this load?
why use INT for a date? i am used to do this with my bulletin board, since i need a 1-second resolution and so i can easily use the time() function in php and format the output string with date(), which is also using unix timestamps. but for applications that only need a resolution of 1 day, something like DATE would be better, i think. for client side, it's more processing to get the date displayed and to do some arithmetics with it (calculate time spans etc.), right? correct me if i'm wrong, since i had some chaotic encounters with DATE and TIMESTAMP values at the beginning of my 'mysql time', and i'm using INT unix timestamps since then... -yves -Ursprüngliche Nachricht- Von: Rudy Metzger [EMAIL PROTECTED] An: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Adam Gerson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 17:19 Betreff: RE: Can mysql handle this load? Why using int for date? Better suited would be DATE or DATETIME (or even TIMESTAMP, depending how you want to use it). For studentid, SMALLINT or MEDIUMINT would maybe suffice, esp when you make them UNSIGNED. For status I would choose CHAR(1), you can put a lot of information into that, which also stays (a bit) human readable. Also enums would be ok but are a mess to change later (in the application). Do yourself a favor and use a master detail relation for this, eg: CREATE TABLE student_status ( Status CHAR(1) NOT NULL,/* short status flag, eg. A */ Verbose VARCHAR(20) NOT NULL, /* verbose description, e.g. ABSENT */ PRIMARY KEY(status) ) Maybe keep 'verbose' on char to force fixed line size and thus faster access. Cheers /rudy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:42 To: [EMAIL PROTECTED]; Adam Gerson Cc: [EMAIL PROTECTED] Subject: Re: Can mysql handle this load? i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can mysql handle this load?
i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -Ursprüngliche Nachricht- Von: Adam Gerson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 15:46 Betreff: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
order of table joins or where clauses relevant?
As we're on this topic in another thread right now: Say I have a SELECT query from more than one table and with some conditions, does it matter in what order I enter the tables in the FROM clause and in what order the WHERE conditions appear in my query? Or does it make any difference if I use WHERE or HAVING? (I see that MS Access likes those HAVINGs...) Of course my tables contain (maybe very much) more than some 100 records and are well-indexed, I believe but that's not my question for now. I guess, the MySQL optimizer reads the table and column names in the specified order and tries to process them the same way, right? Or it joins the tables in my given order... And when are the resulting records reduced by matching against my conditions? Maybe someone can tell me a little bit about performance gains just by doing some 'manual query optimization' :) Yves Goergen www.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining primary field
you mean the primary key (also called primary index), do you? just query 'SHOW INDEX FROM yourtable' and read all rows with Key_name = 'PRIMARY'. you can play around with this in the mysql commandline. you'll get all column names that belong to the primary key in the Column_name field of the returned recordset. at least, that's how it is in mysql 3.23.56-nt... ··· yves at unclassified.de -Ursprüngliche Nachricht- Von: Resolution [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Sonntag, 6. Juli 2003 23:35 Betreff: Determining primary field Hello, I apologize if this is a very simple question but I have spent the last half hour searching for an answer to it and have thus far been unable to find one. I am pretty new to mysql so please be easy on me if this is painfully obvious :) I am trying to determine the primary field in a table through a query from php to my database. I have several tables that could be potentially queried by a particular function in my web site, but the primary field used to identify the ID for different item types are all named specifically to match the table and item type that it is stored in it. My solution to this was to use the primary key in each table as the identifying factor to compare the item ID with the correct field data. I have not been able to find any information on how to obtain the primary field so as to compare its value for each row to the item ID that I want to match. Thank you in advance for any assistance or direction that you could point me in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone running Windows 2000?
running mysql 3.23.56-nt and apache 1.23.-don't-know-exactly on windows 2000 professional sp3 (going to upgrade to sp4 soon) - no problems by now what's your question? :) ··· yves at unclassified.de -Ursprüngliche Nachricht- Von: Ola Ogunneye [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Dienstag, 1. Juli 2003 21:39 Betreff: Anyone running Windows 2000? Is anyone out there running mysql with Apache and php on windows 2000? If you are out there, please respond and hopefully you may be able to help me. I see that most of the posts are geared towards unix/solaris. I am a newbie that needs help. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The quote ' problem...
i don't believe there is another solution, but anyway, how many characters per second can your users type that this string replacement could be a measurable slowdown of the process? -yves -Ursprüngliche Nachricht- Von: harsh [EMAIL PROTECTED] An: My Sql List [EMAIL PROTECTED] Gesendet: Montag, 30. Juni 2003 21:09 Betreff: The quote ' problem... I have to take a text input from users and store it in table,to avoid ' error i can replace ' with \', is there any other solution as that might slow down the process ? harsh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The quote ' problem...
oops, i forgot this one...
isn't that parameter binding only available from mysql 4.1 on?
at least, when i browse through the php doc, it's part of the improved mysql
extension (mysqli), available for mysql 4.1.
does anyone know about version 4.0?
-yves
-Ursprüngliche Nachricht-
Von: Kevin Fries [EMAIL PROTECTED]
An: [EMAIL PROTECTED]; 'harsh' [EMAIL PROTECTED]; 'My Sql List' [EMAIL
PROTECTED]
Gesendet: Montag, 30. Juni 2003 22:26
Betreff: RE: The quote ' problem...
The standard solution is to use binding.
JDBC and Perl DBI both support it, and I imagine most other environments
do as well.
In such case, your query will turn from:
INSERT INTO someTable values ('foo\'')
Into:
INSERT INTO someTable values (?)
You're use a prepared statement, and before executing it, you'll set the
value of your variable.
Read the mysql manuals for whatever language's interface you're using.
Kevin
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, June 30, 2003 12:26 PM
To: harsh; My Sql List
Subject: Re: The quote ' problem...
i don't believe there is another solution,
but anyway, how many characters per second can your users
type that this string replacement could be a measurable
slowdown of the process?
-yves
-Ursprüngliche Nachricht-
Von: harsh [EMAIL PROTECTED]
An: My Sql List [EMAIL PROTECTED]
Gesendet: Montag, 30. Juni 2003 21:09
Betreff: The quote ' problem...
I have to take a text input from users and store
it in table,to avoid ' error i can replace ' with \',
is there any other solution as that might slow down the process ?
harsh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--
MySQL General Mailing
List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedures in mysql5
insane you mean extremely fast? or easy to use? i'd like to know what those SPs are good for... -yves -Ursprüngliche Nachricht- Von: electroteque [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Montag, 30. Juni 2003 00:09 Betreff: RE: stored procedures in mysql5 thanks this is pretty insane cant wait for its release :D -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, June 30, 2003 6:07 AM To: electroteque Cc: Mysql Subject: Re: stored procedures in mysql5 On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote: ok i have a bleeding edge server instance on my server apache2/php5/mysql5 running alongside the stable server , is stored procedures actually functioning in it yet ? is this the correct syntax there is no documentation for it yet. create procedure test as select * from tablename; There are a lot of SP examples in the source distribution or BK tree. Have a look at mysql-test/t/sp.test. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement with an ' in it
why don't you just insert your values after you escaped some special characters? specifically, you have to replace all ' by \' (prepend a single backslash character), and everything works fine! i guess you do your INSERTs from out of some programming language, like PHP, Perl or C. just use the appropriate str_replace() functions of that language to replace those apostrophes. -yves -Ursprngliche Nachricht- Von: Zachary Perschall [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Samstag, 28. Juni 2003 20:24 Betreff: Insert statement with an ' in it Help! I'm trying to do an insert statement where one of the fields sometimes contains an apostrophe. The field type is a varchar. Everytime that one of these values comes up with an apostrophe, it tells me there is an error in my SQL statement (obviously because it thinks there are more information than there are fields I'm inserting into) I'm sure this is something simple, but I can't seem to find the answer on the web. Please help! -Zach -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW DATABASES user rights
thanks for your hint in that direction! i'm currently using the latest mysql 3.23, but i guess, i'll upgrade to 4.0 before i get my server up and running. but what i found, and may be more appropriate for my needs is the --safe-show-database option. i just added it to /etc/my.cnf, restarted the server and things were fine again :-) so that's what i did to my.cnf: [mysqld] safe-show-database HTH anyone else, too... yves at unclassified.de -Ursprngliche Nachricht- Von: Victoria Reznichenko [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 26. Juni 2003 11:25 Betreff: Re: SHOW DATABASES user rights [EMAIL PROTECTED] wrote: I have a MySQL server running with some users besides root, that I want to grant all privileges for their own databases, but no rights for anything else. So to say, I have a user web01 that shall be able to do whatever he wants with the database db01. There are more databases, like db02 etc. If user web01 runs a 'SHOW DATABASES' command, he'll get to see ALL databases on the server, but I don't want him to see them... When the user does a 'USE dbname;', it says 'access denied'. There must be some special trick to let him only see the database(s) he's got rights on. (At least my webhoster managed that somehow...) Anyone an idea what rights I have to assign to get this running? Since 4.0.0 user must have SHOW DATABASES privilege to see databases on with he has no permissions: http://www.mysql.com/doc/en/Privileges_provided.html In 3.23.xx you can run mysqld with --skip-show-database option: http://www.mysql.com/doc/en/Command-line_options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
hm? i just did a little test on mysql 3.23.56-nt, and had no problems setting a null value on a null column having a default value other than null. it turned to be null, rather than the default value. ··· yves at unclassified.de -Ursprüngliche Nachricht- Von: danchik [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 26. Juni 2003 23:20 Betreff: Re: can you insert null? your statement will do just that, but make sure the field has no default value and allows NULL - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
hm, no, it works fine with int and varchar(40) -Ursprüngliche Nachricht- Von: danchik [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Freitag, 27. Juni 2003 00:01 Betreff: Re: can you insert null? was the field type varchar? because it seems that no default varchars set the NULL or (NULL) as a literal (NULL) not a binary 0 for some reason. - Original Message - From: [EMAIL PROTECTED] To: danchik [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 2:31 PM Subject: Re: can you insert null? hm? i just did a little test on mysql 3.23.56-nt, and had no problems setting a null value on a null column having a default value other than null. it turned to be null, rather than the default value. ··· yves at unclassified.de -Ursprüngliche Nachricht- Von: danchik [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 26. Juni 2003 23:20 Betreff: Re: can you insert null? your statement will do just that, but make sure the field has no default value and allows NULL - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW DATABASES user rights
Hello, I have a MySQL server running with some users besides root, that I want to grant all privileges for their own databases, but no rights for anything else. So to say, I have a user web01 that shall be able to do whatever he wants with the database db01. There are more databases, like db02 etc. If user web01 runs a 'SHOW DATABASES' command, he'll get to see ALL databases on the server, but I don't want him to see them... When the user does a 'USE dbname;', it says 'access denied'. There must be some special trick to let him only see the database(s) he's got rights on. (At least my webhoster managed that somehow...) Anyone an idea what rights I have to assign to get this running? yves at unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
