Determining Table Types
how to determine the type of a table that has been created previously? "describe [tablename]" doesn't seem to tell you whether it's MyISAM, or InnoDB, or what.
View Complete Forum Thread with Replies
Related Forum Messages:
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
Data Types :: Blob And Text Returns Only Value 252 $handle->types
I'm writing a perl script that will among other things collect data types from a mysql database. I'm using mysql.pm 2.9002 to retrieved types information from the statement handle. The problem is that all blob and text fields return the value 252 when using the $handle->types function and I need to differentiate between them. Has anyone else dealt with this issue?
View Replies !
Mixed Table Types
Here's one of those damned annoying "what if" questions for everyone. If I have a transaction that performs INSERTs or UPDATEs on a bunch of tables, some InnoDB and some MyISAM, and after performing a bunch of modifications on each I issue a ROLLBACK, does the fact that a MyISAM table is involved in the process cause any problems with the ROLLBACK at all? Any definitive answer either way would be appreciated, as I have just inheritted the codebase from hell to either maintain or redo parts
View Replies !
MySQL Table Types
Looking through the documentation it's not very clear which table types would be the best to use. Could somebody out there clear up the differences between them and which would be best for the different types of purposes.
View Replies !
Mixed Table Types In Transactions?
If I have a transaction that performs INSERTs or UPDATEs on a bunch of tables, some InnoDB and some MyISAM, and after performing a bunch of modifications on each I issue a ROLLBACK, does the fact that a MyISAM table is involved in the process cause any problems with the ROLLBACK at all?
View Replies !
Limits On Table Types And Database?
I have been using the mysql database in my project. It recently came to mind that their may be limits on how much data a table or database itself can hold. I did some googling on it. I found this on table types - InnoDB has no limit MyIsam is limited to 4gb - but I found a site that showed how the limit could be increased. So there are size limits on certain types of tables - but what about the database itself? I have a database that consists of a large amount of tables. Some tables are holding bytes (column type = mediumblob; to store images) in their rows. I'm concerned that when I go live with my project that I might run into problems if a limit is reached or exceeded.
View Replies !
Referential Integrity, FULLTEXT And Table Types
I need to have a table that supports FULLTEXT searches. This implies that this table should be a MyISAM table. However, I also require that this table act as a parent for child tables in order to support referential integrity. If I create the child tables as INNODB tables, will referential integrity still work with the MyISAM parent table?
View Replies !
Finding Primary Key, Data Types Used In A Table
Is there an easy way to get the name of the column that is the primary key of a MySQL table (using a MySQL query of some sort)? Also, is there an easy way to get the data type that is associated with any given column name in a MySQL table (using a MySQL query of some sort)?
View Replies !
Proper Table Setup For Multiple User Types
I have a table called "schedules". I want to combine 2 types of schedules into this table(if possible) .. class schedules(class_id) .. and individual user schedules(user_id) With DRY in mind..I don't want to have a class schedules table and user_schedules(as I will have other tables using the same idea as well), as both schedule setups will be the same. I have heard in the past that it is improper syntax to include both a "class_id" & "user_id" field in each table, where 1 would be null(I.E. a class_id would be set, and user_id would be null). Is there a simple logic behind this type of thing that I am just completely blind of?
View Replies !
Determining Rank?
I currently have a database that I pull team stats out of. Such as points, goals for, goals against, fouls, etc... I was looking for the best way to get from MySQL the rank of each team. So for example, ranking the teams based on points, such as: Team 1 - 20 points ... would display (Tied for 1st) Team 2 - 20 points ... would display (Tied for 1st) Team 3 - 18 points ... would display (3rd place) Team 4 - 17 points ... would display (4th place) and so on... What I am using right now is just a select and ordring the results by points. I tried using PHP to give me what I wanted (like check previous result, if its the same then increment a counter, and so on), but I was wondering if there was a way to do this using MySQL? (Im learning every day MySQL can do some wonderful things!) This is my MySQL statement I am using: ........
View Replies !
Determining Number Of Days
I'm trying to determine the number of days between today and the value in a datetime field in a table. It appears my version of MySQL doesn't have DATEDIFF available, so I'm wondering if there's another method to achieve the same result as: SELECT DATEDIFF(CURDATE(),`mydate`) AS numdays FROM tblArticles WHERE artid = 10 .
View Replies !
Determining Future Dates
I have a mysql statement Code: $result1 = mysql_query("SELECT *, UNIX_TIMESTAMP(startingdate) as startingdate FROM whatsuplistings where MONTH(startingdate) >= '$currentmonth' and YEAR(startingdate) >= $currentyear and sectionid= 2 order by startingdate asc ") or die("Couldn't ececute query.".mysql_error()); This works for the current year, but I cant seem to get this to work if the year is 2005 because the current month which is 10 is smaller than the first month which is 1 in 2005.
View Replies !
Determining Which Age Ranges Have People In Them
Making up another example here since it makes things clearer for everyone. I have a people table. One of the fields in there is 'age' (as an integer). I want to output a series of links like the following: People by age: 21-30 31-40 51-60 Each range would be a link to a listing of people in that age range. They stumbler for me is that I want to only show age ranges that have people in the table in that age range. So above, the 41-50 range didn't show up because nobody in the people database had an age in that range. I don't know how to do this without 10-11 queries each checking a range and counting the numrows returned. And I just know if I hardcode the last year to 150, there will suddenly be a need for a 151-160 range. Any elegant way to do this without a loop or a dozen+ queries?
View Replies !
Determining If A Substring Exists In Field
How can I determine if a substring exists in some comma delimited data using sql? I need to return records where a word is found in a list of words from a field. Rather than returning every record and then doing split() and searching myself I would like to know if I can do it using sql.
View Replies !
Determining The Most Appearing Thing In A Column
I have a statistics table, and it has a column called page. I want to find out what page is visited the most. Every time someone goes to any page it adds a row into the table with whatever page they are on. What query could i use to find the most visited page?
View Replies !
Please Explain MySQL Distinct Table Types (was "Where To Use ?")
These are the MySQL distinct table types. MyISAM MERGE ISAM HEAP InnoDB BDB or BerkeleyDB Tables so please anyone help me to find out the use of each n every type. it will be very helpfull for me if you people can provide simple meanings rather than technical. For Ex : why to use MERGE over HEAP or something like this Your help is most welcome
View Replies !
ROW TYPES
I'm trying to use the "CREATE ROW TYPE name" feature that SQL has, but it doesn't seem like its supported in MySQL. Does anyone know what I can use to create a cell with multiple fields? Basically a row nested within a row. I am also trying to use the array type like "Phone CHARACTER VARYING (15) ARRAY [5]," when creating a column within a table. I would like to have multiple values (phone numbers) for this column field. This is another feature that I know SQL has but it gives me an error when I try it in MySQL. Do I need a different version of MySQL? I'm using the free 5.0.37 version.
View Replies !
Column Types
I've just moved out of the access suburbs and into MySQLville. I'm having trouble understanding the column types and when to use which one. Can anyone give me a short rundown in plain english how to understand them. To point out some areas: the ENUM and SET... when to use them. Which BLOB or TEXT type to use. To give an idea of what I'm designing: a job board, similar to Monster.com
View Replies !
Summing With Different Types
I have a table that sort of logs everything. It's structure is basically divided into 3 columns: date, type, points I have like 3 different types but I want to output the data as: date, points_from_type1, points_from_type2, points_from_type3 basically points_from_type1 is the sum of the points accumulated for that day same for type 2 and 3. Is there any way of doing this easily? The best way I can think of doing this is creating 3 different tables with 3 different queries of "select date, sum(points) from table_1 where type = 1 group by date;" and merging them together.
View Replies !
Two Types Of Sorting
I have a database which has these colums: id, weith and date f. I want to select some of my datas but in this way. If the diffrecence between today and the date that is stored into database is less then 30 days, then it must sort first by weith and after that by date. If the diffrences is more that 30 days, then it must sort first by date and after that by weith.
View Replies !
Enum Types
I would like to know if it is possible and how to have an extra box on your webpage so, if in your enum field the type that the person wants is not there then they write it in the next field. When they click submit it is automatically updated so next time someone comes a long they can click the drop down list and select that one. Also is there anywhere you can get pre made enum lists for country, county etc.....
View Replies !
Map JDBC Types
I'm setting up a server-application on a tomcat that is developed for SQL Server or Oracle but I got the thankless task of trying to set up the application on a MySQL instead. One part of setting up this is to port some properties-files and I cannot get the hang of it. Is there some friendly soul out there that could give me a hand? I've included the content of properties-files below for SQL Server and I believe you'll get how it should be ported to MySQL. Most part of the content is the same in all three files but I included them all anyway
View Replies !
Field Types
I can't seem to find a built in function or view that will return all the available field types. I know I can piece together a list from the docs, but I don't want to have to do that everytime a new version of Mysql comes out(if possible)
View Replies !
All Types In A Column
I have a column in my table called 'catagories'. In this column, my rows have different data for this section. They say: Phosothop, PHP, Flash...etc... How can i export the column and compare them and take only the unique catagories, and order them in alphabetical A=>Z? Is there an easier way to do this? It seems like if i do this, with a big table, it could potentially take a while to do.
View Replies !
Best Data Types
i would very much like to know, what the most efficient/suitable Data Types would be used with the following data:? Account Details: Email Address Password Email Updates? Interested Sites (6 Check Boxes Max) Personal Details: Title (Mr, Mrs, Miss, Ms, Dr) First Name Last Name Image Title Upload Image Address: Property Name/Number Street Name Location Town/City Country Postcode Telephone Work Mobile
View Replies !
Switching Indexing Types
I am still a novice when it comes to MySQL and I was wondering if switching the indexing from INDEX to a FULLTEXT would have any dramatic repercussions on current searches based on the the INDEX format? I have 3 indexed fields: unit_id, customer_id, facility_id all in one table called units. I would like to do text searches unit ids but also account for user mistakes, like incomplete unit ids and return all possible matches. Doing some research on the MySQL site, they have lots of examples based on FULLTEXT indexed fields.
View Replies !
Boolean Variable Types
i guess this can account to more than just BOOL database variables. bassicaly if i have a boolean type variable which represents an on or off state of something. ie user_is_banned should i set it to null and change it to 1 when/if i ban this user. or is it equally as efficent to use 0 or null? just curious becasue in some of my tables i have 0 meaning off and other ones i have null meaning off. i want to change it to all one way or the other to create consistancy. i just want to know if theres a reson to go one way or the other. personally it feels like using null is better because doesnt null take up less space than 0? but i guess thats only in theory and null takes up the same amount of space, im not sure.
View Replies !
BOOLEAN Data Types
I don't see any data type for boolean so I used ENUM ('true','false') but that causes a lot of convert.toboolean mess thru-out the code
View Replies !
Data Types For Calculations
If I need to have a column with both int and text, can that be done (if so, what's the best way?), and what are the potential pitfalls? I will want to perform calculations on the data in the entire column (like compute the average of the column), so would having text in one or more of the rows cause a problem with the calculations?
View Replies !
Comparison Of Data Types
I have a large map that is represented in a sql database with an incremental index and a value for each entry representing the type of area on the map.I'm looking to optimize the code that stores and retrieves this information.In an attempt to minimize the number of characters that are sent out from the db, I am storing the types as ascii characters that represent the number type and converting them to numbers once retrieved. In this way, I hope to speed it up by not having to send two-digit numbers.So, my question is whether or not this is a good approach. Would sending just simple numbers be just as fast or faster?Or, more basically, I need a comparison of mysql performance when handling the various types of data. it seems like such a comparison has to exist somewhere.
View Replies !
Data Types For Currency
I'm wondering if someone would please give me a URL for the MySQL datatypes. For example, I am trying to create a field within a table to contain currency data.
View Replies !
Field Types Conversion
I am converting a rather large database to mysql. Currently all the keys in the existing database are of type BIT(128). The dump file that I am importing from has the keys in hexadecimal format such as: X7330F07EDBE569A491796DBAC0A80006. Now I have not been able to find an equivalent field type in mysql so we decided to use CHAR(32) for all the keys. I was able to import the database and get it working. However after testing/comparing to existing database we find that mysql is much slower then the existing database.
View Replies !
Changing Column Types
I am planning to change the column type of one of my tables (database engine MyISAM) from mediumblob to long blob. I have two concerns reagarding this a) Will all the existing data be preserved ? (I searched in the online documentation, what I found was a vague statement about MySql trying to preserve the data). I tried a few experiments, MySql did not throw any errors(with strict mode) or warnings and earlier data was retained. b) Performance hit ? Is the performance hit limited to an extra byte in the column (to accomadate the size value) ? The column is not indexed.
View Replies !
Data Types/Java
If I want "users" to have a list of "assignments", how can I implement this? Also, i'm trying to access a mysql database from a java program.
View Replies !
MySQL Data Types
Does anyone know a good reference to tips on how to use MySQL Data types??? Example: Should I use CHAR for a unique ID number field? if so, what are the recommended length/values, attributes, null type, default value or extras? I want to save long news articles into MySQl DB using TEXT data type, but what lenght/values, attributes, null type and default value should i use??
View Replies !
COBOL Data Types
Is there anybody else out there using COBOL and MySQL? Does anybody have a list of equivalent data types between (MF) COBOL and MySQL? I have found that an INT is converted successfully using PIC S9(9) COMP. Is there a list in existence? or a method to work it out.
View Replies !
Schema For Different Types Of Tables
Is there a website that offers schema for different types of tables. for example the code to create a table in MySQL 5 that you would use to keep data for registered users? heres one I made myself Create table users( login VARCHAR(40), NOT NULL password VARCHAR(40), NOT NULL first VARCHAR(40), NOT NULL last VARCHAR(40), NOT NULL address VARCHAR(40), NOT NULL zip VARCHAR(40), NOT NULL city VARCHAR(40), NOT NULL age SMALLINT, NOT NULL sex VARCHAR(10), NOT NULL phone INT, NOT NULL email VARCHAR(10), NOT NULL
View Replies !
Classifying Data Types
I'm not sure how to classify some the data I've entered for certain fields so I left them at VarChar when I entered them. I'd like to be able to organize them correctly and such and certain ones will not with that setting. $900,000 How would I classify a value like this? I tried doing it as an integer and the $ causes the value to be voided and it goes to zero. I didn't see anything for dollar amounts in the database. I also have a field for year, for example 2009. However some of the data I'm dealing with has two values and I generally put it like this 2009/2010, I cannot change the default to 9 characters and it will trim it to look like this 2009.
View Replies !
Difference Between Types Of Joins
I have two tables menu & page_content (the relationship is many page_content records to one menu record). I set up a query to list all the records, worked fine on my local pc but did not when I loaded it to the server, after doing some reading I changed it to a "RIGHT JOIN" and it worked. Now I've done some reading and googleing and this stuff is still not making sense, My question is what is the difference between??? 1. LEFT JOIN 2. RIGHT JOIN 3. INNER JOIN 4. OUTTER JOIN and which one should I use??? Code:
View Replies !
Indexes And Why NOT To Mix Field Types
In a WHERE clause, if you are mixing field types between an INT field and a VARCHAR field, MySQL won't use the otherwise appropriate index! Your indexes essentially become worthless in this case! So when you relate tables, make sure your relate-fields are in INT form instead of VARCHAR form if they are relating to an INT field. And for so damn long I couldn't figure out why MySQL never used any of my indexes...
View Replies !
Db Types (innoDB, MyISAM....)
so does anyone have any good resources that would explain the differences between the types of mysql databases, and moreover what the benefits are of each one. I am looking for womthing that is a bit easier to read an understand than the MySql documentation.
View Replies !
Different Types Of Joins In MySQL
I would like to ask what is the difference between all the various kinds of joins on MySQL. I've read this page on the official MySQL manual but I didn't quite get it. The most important part of this questions is when should I use each kind of join in order to use less resources?
View Replies !
|
TRACKER
