<p>Hello Everyone,</p>
<p>I'm a database administrator and I'm starting to learn Golang.</p>
<p>I'm trying to learn how to retrieve an unknown number of rows from an unknown number of columns within in MySQL database table.</p>
<p>I have the following <a href="https://gist.github.com/akalaj/015aa79b5854b728af0baf884f50b827" rel="nofollow">MySQL Test Table</a></p>
<p>I am trying to fetch all the data in the above table using the instructions from the bottom of <a href="http://go-database-sql.org/varcols.html" rel="nofollow">This Guide</a></p>
<p>I want to run <em>fmt.Println(values)</em> against all the values from my example table. But I'm lost as to how to print the values.</p>
<p>Here's the code I've sewn together => <a href="https://gist.github.com/akalaj/28684690c42da8e9aa0cea5bd7340b1c" rel="nofollow">MyCode</a></p>
<p>Can anyone look at "MyCode" and tell me how I can simply print the values to the terminal?</p>
<p>Any help would be greatly appreciated.</p>
<hr/>**评论：**<br/><br/>titpetric: <pre><p>Take a look at <a href="https://github.com/jmoiron/sqlx" rel="nofollow">sqlx</a>, and <a href="http://jmoiron.github.io/sqlx/" rel="nofollow">it's documentation</a>. Generally, you'd want to scan query results into a structure that matches what you're querying.</p>
<p>This would be a minimal (and very much shorter) example, of retrieving a multi-row query result: <a href="https://github.com/titpetric/books/blob/master/api-foundations/chapter6b/main_sqlx.go" rel="nofollow">github example</a>. The example is from <a href="https://leanpub.com/api-foundations" rel="nofollow">API Foundations in Go</a>. I'm using davecgh/go-spew to print the output, but you could use something as simple as a loop:</p>
<pre><code>for index, row := range databases {
fmt.Printf("index %d, value %#v\n", index, row)
}
</code></pre>
<p>I like to use <code>%#v</code> here because it doesn't print only the value, but the type of the value as well. Here's a link to the std lib documentation for <a href="https://golang.org/pkg/fmt/#hdr-Printing" rel="nofollow">Printf parameters</a>.</p>
<p>p.s. it's good you're learning go and very ambitious that you started with MySQL right away. But maybe you'll want to know about some more basic things so you get a feel for the programming language itself, before you try applying it to real world use cases. A good place to start and get a more complete experience is <a href="https://gobyexample.com" rel="nofollow">Go by example</a>.</p></pre>orthodox_caveman_: <pre><p>Awesome. This is really good info. Thanks!</p></pre>iroflmaowtf: <pre><p>did you try <code>fmt.Println(vals)</code> ?</p>
<pre><code>vals := make([]interface{}, len(cols))
for i, _ := range cols {
vals[i] = new(sql.RawBytes)
}
for rows.Next() {
err = rows.Scan(vals...)
// Now you can check each element of vals for nil-ness,
if err != nil {
log.Fatal(err)
}
// Here is where I get lost
// How should I access Vals and print the values it finds???
//
// try this
//
fmt.Println(vals)
}
</code></pre></pre>orthodox_caveman_: <pre><p>Ahh, soo close.</p>
<p>So that's working, but it's printing memory addresses instead of string values.</p>
<p>Seems I need to have a conversion somewhere.</p>
<p>Here's the code that is working so far....Where do I need to make a conversion at? At the interface level? Is there a to-a-string function I can run inside of the fmt.Println() command?</p>
<p><a href="https://gist.github.com/akalaj/a8087ef8a23f21de3240717862624f58" rel="nofollow">This is my current ALMOST working code</a></p></pre>iroflmaowtf: <pre><p>in stead of <code>fmt.Println(vals)</code></p>
<p>try</p>
<pre><code>for rows.Next() {
err = rows.Scan(vals...)
// Now you can check each element of vals for nil-ness,
if err != nil {
log.Fatal(err)
}
// Here is where I get lost
// How should I access Vals and print the values it finds???
//
// try this
//
//fmt.Println(vals)
// loop over values
for _, v := range vals {
// print out each field's value
fmt.Printf("%v, ", v)
}
// add a new line
fmt.Printf("\n")
}
</code></pre></pre>IAmSlar: <pre><p>I didn't look to closely at the other replies, you might have your answer already.</p>
<p>If not the mssql go driver got a good example:</p>
<p><a href="https://github.com/denisenkom/go-mssqldb/blob/master/examples/tsql/tsql.go" rel="nofollow">https://github.com/denisenkom/go-mssqldb/blob/master/examples/tsql/tsql.go</a></p>
<p>The printValues function is probably what you want.</p></pre>tsdtsdtsd: <pre><p>If I may ask: why don't you know about your cols? Would it be easier to work something out with 'SHOW COLUMNS' maybe?</p>
<p>You can range over your vals slice like this:
<code>
for _, value := range vals {
fmt.Println(value)
}
</code>
but you would need to convert the interface type to a proper type to be able to work with them IMO (depending on what you want to do with the values afterwards). </p></pre>orthodox_caveman_: <pre><p>Thanks! Appreciate the info.</p>
<p>So the reason I don't know about my COLs is because MySQL has this frustrating habit of creating tables and columns on the fly when looking at system info, and these tables and columns change all the time.</p>
<p>For example...in MySQL, there is a tool called "EXPLAIN."</p>
<p>You can add the phrase "EXPLAIN" to the start of every query and MySQL will "explain" what the query is doing at the index and table level.</p>
<p>Typically , "EXPLAIN'ing" a query yields 10 rows...but sometimes it's 9 and sometimes it's 11, depending on the query.</p>
<p>And that's just EXPLAIN. The same issue arises when we execute "SHOW SLAVE STATUS" or "SHOW ENGINE INNODB STATUS."</p>
<p>I love Golang's concurrency features, and for administrating 100s of servers running many different instances of MySQL, the ability to talk to many servers at once, without losing track of who's responding, is really appealing to me.</p>
<p>I'd like to see if there is a way I can write dynamic Golang code to handle variable numbers of columns and rows so I can effortlessly gather system feedback, from many systems, without having to create a struct to receive data for every single administrative command MySQL has.</p>
<p>I figure...if I can figure out how to dynamically handle columns and rows from a small test table (like the test table I have in my original post), then I can do so for tables that are generated on the fly, like the "EXPLAIN" table in MySQL.</p>
<p>It's challenging because it doesn't seem like there is a single way to handle variable rows and columns.</p>
<p>It seems like the SQL package with Golang was written with the expectation to handle explicit numbers of columns and rows.</p></pre>titpetric: <pre><p>At least for EXPLAIN you can create a struct with all the possible fields (sometimes 11 ;)), and whenever it will return less than that, the unused fields will be left at their default empty value. Checking if you have that value is usually as simple as <code>if struct.Val != "" { ...</code>. The struct doesn't have to match exactly to what is returned, it can be a superset of that.</p>
<p>Edit: again, this might be sqlx specific. I haven't used the default sql/database with the mysql driver, as sqlx felt more natural in features from the beginning. Definitely give sqlx a try.</p></pre>
